XQuery and XQSE Developer’s Guide

     Previous  Next    Open TOC in new window    View as PDF - New Window  Get Adobe Reader - New Window
Content starts here

XQuery-SQL Mapping Reference

This appendix provides the details of Oracle Data Service Integrator core support and base support for relational data, and includes these topics:

Each section that follows includes information about:

 


IBM DB2/NT 8 (and higher)

The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for IBM DB2/NT 8.

Data Type Mapping

Table G-1 lists supported data type mappings for IBM DB2/NT 8.

Table G-1 IBM DB2 Data Type Mappings
DB2 Data Type
XQuery Type
BIGINT
xs:long
BLOB
xs:hexBinary
CHAR
xs:string
CHAR() FOR BIT DATA
xs:hexBinary
CLOB1
xs:string
DATE
xs:date
DOUBLE
xs:double
DECIMAL(p,s)2 (NUMERIC)
xs:decimal (if s > 0), xs:integer (if s = 0)
INTEGER
xs:int
LONG VARCHAR1
xs:string
LONG VARCHAR FOR BIT DATA
xs:hexBinary
REAL
xs:float
SMALLINT
xs:short
TIME3
xs:time4
TIMESTAMP5
xs:dateTime4
VARCHAR
xs:string4
VARCHAR() FOR BIT DATA
xs:hexBinary

1Pushed down in project list only.

2Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).

3Accurate to 1 second.

4Values converted to local time zone (timezone information removed) due to TIME and TIMESTAMP limitations. See “Date and Time Data Type Differences: Timezones and Time Precision” on page 3-6 for more information.

5Precision limited to milliseconds.

Function and Operator Pushdown

Table G-2 lists functions and operators that are pushed down to IBM DB2/NT8 RDBMSs. See “fn-bea:sql-like” on page 2-26 for details about two-argument and three-argument versions of the fn-bea:sql-like() function.

Table G-2 IBM DB2 Functions and Operators  
Group
Functions and operators
Logical operators
and, or, not
Numeric arithmetic
+, -, *, div, idiv1
mod2
Numeric comparisons1
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge
Numeric functions
abs, ceiling, floor, round
String comparisons3
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge
String functions
concat, upper-case, lower-case, substring(2,3)4, string-length, contains5, starts-with5, ends-with5, fn-bea:sql-like(2,3) fn-bea:trim6, fn-bea:trim-left6, fn-bea:trim-right6, fn-bea:repeat6, fn-bea:pad-left6, fn-bea:pad-right6
Datetime comparisons
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xs:time
Datetime functions
year-from-dateTime, year-from-date, month-from-dateTime, month-from-date, day-from-dateTime, day-from-date, hours-from-dateTime, hours-from-time, minutes-from-dateTime, minutes-from-time, seconds-from-dateTime, seconds-from-time, fn-bea:date-from-dateTime, fn-bea:time-from-dateTime
Aggregate
min, max, sum, avg, count, count(distinct-values)
Other
empty, exists, subsequence7

1All numeric types.

2xs:integer (and subtypes) only.

3Arguments must have SQL data type CHAR or VARCHAR.

4If second and third arguments are types xs:double or xs:float, they cannot be parameters.

5Second argument must be a constant or a parameter.

6Argument must be SQL data type CHAR or VARCHAR.

7Both two- and three-argument variants supported.

Cast Operation Pushdown

Table G-3 lists supported cast operations.

Table G-3 IBM DB2 Cast Operations  
Source XQuery Type
Target XQuery Type
numeric
xs:double
numeric
xs:float
numeric
xs:int
numeric
xs:integer
numeric
xs:short
xs:decimal (and subtypes)
xs:string
xs:integer (and subtypes)
xs:decimal
xs:string
xs:double
xs:string
xs:float
xs:string
xs:int
xs:string
xs:integer
xs:string
xs:short
xs:dateTime
xs:time

Other SQL Generation Capabilities

Table G-4 lists common query patterns that can be pushed down. See also “Common Query Patterns”.

Table G-4 IBM DB2 Other SQL Generation Capabilities
Feature
Description
If-then-else
yes
Inner joins
yes, SQL-92 syntax
Outer joins
yes, SQL-92 syntax
Semi joins, Anti semi joins
yes
Order by
yes
Order by: Empty (NULL) order supported
Fixed (always sorts NULLs high). Order-bys with “empty least” modifier (the XQuery default) are not pushed down.
Order by: Aggregate function in ordering expression
yes
Group by
yes
Distinct pattern
yes
Trivial aggregate pattern
yes (using GROUP BY constant)
Direct SQL composition
yes

 


Microsoft SQL Server 2000 (and higher)

The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Microsoft SQL Server 2000.

Data Type Mapping

Table G-5 lists supported data type mappings for Microsoft SQL Server 2000.

Table G-5 SQL Server 2000 Data Type Mapping  
SQL Data Type
XQuery Type
BIGINT
xs:long
BINARY
xs:hexBinary
BIT
xs:boolean
CHAR
xs:string
DATETIME1
xs:dateTime2
DECIMAL(p,s)3 (NUMERIC)
xs:decimal (if s > 0), xs:integer (if s = 0)
FLOAT
xs:double
IMAGE
xs:hexBinary
INTEGER
xs:int
MONEY
xs:decimal
NCHAR
xs:string
NTEXT4
xs:string
NVARCHAR
xs:string
REAL
xs:float
SMALLDATETIME5
xs:dateTime
SMALLINT
xs:short
SMALLMONEY
xs:decimal
SQL_VARIANT
xs:string
TEXT4
xs:string
TIMESTAMP
xs:hexBinary
TINYINT
xs:short
VARBINARY
xs:hexBinary
VARCHAR
xs:string
UNIQUIDENTIFIER
xs:string

1Fractional-second-precision up to 3 digits (milliseconds). No timezone.

2Values converted to local time zone (timezone information removed) and fractional seconds truncated to milliseconds due to DATETIME limitations. See “Date and Time Data Type Differences: Timezones and Time Precision” on page 3-6 for more information.

3Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).

4Pushed down in project list only.

5Accuracy of 1 minute.

Additionally, the following XQuery data types can be passed as parameters or returned by pushed functions:

Function and Operator Pushdown

Table G-6 lists functions and operators that are pushed down to Microsoft SQL Server 2000. (See “fn-bea:sql-like” on page 2-26 for details about two-argument and three-argument versions of the fn-bea:sql-like() function.)

Table G-6 SQL Server 2000 Function and Operator Pushdown  
Group
Functions and Operators
Logical operators
and, or, not
Numeric arithmetic
+, -, *, div, idiv1
mod2
Numeric comparisons1
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge
Numeric functions
abs, ceiling, floor, round
String comparisons3
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge
String functions
concat, upper-case, lower-case, substring(2,3)4, string-length, contains5, starts-with5, ends-with5, fn-bea:sql-like(2,3)4, fn-bea:trim, fn-bea:trim-left, fn-bea:trim-right, fn-bea:repeat, fn-bea:pad-left, fn-bea:pad-right
Datetime comparisons
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xdt:yearMonthDuration, xdt:dayTimeDuration
Datetime functions
year-from-dateTime, year-from-date, years-from-duration, month-from-dateTime, month-from-date, months-from-duration, day-from-dateTime, day-from-date, days-from-duration, hours-from-dateTime, hours-from-duration, minutes-from-dateTime, minutes-from-duration, seconds-from-dateTime, seconds-from-duration, fn-bea:date-from-dateTime
Datetime arithmetic
op:add-yearMonthDurations, op:add-dayTimeDurations, op:subtract-yearMonthDurations, op:subtract-dayTimeDurations, op:multiply-yearMonthDuration, op:multiply-dayTimeDuration, op:divide-yearMonthDuration, op:divide-dayTimeDuration, subtract-dateTimes-yielding-yearMonthDuration, subtract-dateTimes-yielding-dayTimeDuration, op:add-yearMonthDuration-to-dateTime, op:add-dayTimeDuration-to-dateTime, op:subtract-yearMonthDuration-from-dateTime, op:subtract-dayTimeDuration-from-dateTime, subtract-dates-yielding-yearMonthDuration, subtract-dates-yielding-dayTimeDuration, op:add-yearMonthDuration-to-date, op:add-dayTimeDuration-to-date, op:subtract-yearMonthDuration-from-date, op:subtract-dayTimeDuration-from-date
Aggregate
min, max, sum, avg, count, count(distinct-values)
Other
empty, exists, subsequence6

1For all numeric types

2For xs:integer and its subtypes only.

3Arguments must be of SQL data type CHAR, NCHAR, VARCHAR, or NVARCHAR.

4Both the 2-argument and 3-argument versions of function supported.

5Second argument must be SQL data type CHAR, NCHAR, VARCHAR, or NVARCHAR.

6Only the three-argument variant of fn:subsequence is supported, with the additionl requirement that the $startingLoc must be 1 (constant) and $length must be xs:integer type.

Cast Operation Pushdown

Table G-7 lists supported cast operations.

Table G-7 SQL Server 2000 Cast Operations
Source XQuery Data Type
Target XQuery Data Type
numeric
xs:string
numeric
xs:double
numeric
xs:float
numeric
xs:integer
numeric
xs:long
numeric
xs:int
numeric
xs:short
xs:integer (and subtypes)
xs:decimal
xs:string
xs:double1
xs:string
xs:float
xs:string
xs:integer
xs:string
xs:long
xs:string
xs:int
xs:string
xs:short
xs:dateTime
xs:date
xs:dateTime
xs:string

1Source SQL type must be CHAR, NCHAR, VARCHAR, or NVARCHAR.

Other SQL Generation Capabilities

Table G-8 lists common query patterns that can be pushed down. (See “Common Query Patterns” for details.)

Table G-8 SQL Server 2000 Other SQL Generation Capabilities
Feature
Description
If-then-else
yes
Inner joins
yes, SQL-92 syntax
Outer joins
yes, SQL-92 syntax
Semi joins, Anti semi joins
yes
Order by
yes
Order by: Empty order (NULL order)
fixed (always sorts NULLs low). Order-bys with "empty greatest" modifier are not pushed down.
Order by: Aggregate function in ordering expression
yes
Group by
yes
Distinct pattern
yes
Trivial aggregate pattern
yes (using subquery)
Direct SQL composition
yes

 


Oracle 8.1.x

The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Oracle 8.1.x (Oracle 8i).

Data Type Mapping

Table G-9 lists supported data type mappings for Oracle 8.1.x (Oracle 8i).

Table G-9 Oracle 8.1.x Data Type Mapping
Oracle 8 Data Type
XQuery Type
BFILE
not supported
BLOB
xs:hexBinary
CHAR
xs:string
CLOB1
xs:string
DATE2
xs:dateTime
FLOAT
xs:double
LONG1
xs:string
LONG RAW
xs:hexBinary
NCHAR
xs:string
NCLOB1
xs:string
NUMBER
xs:double
NUMBER(p,s)3
xs:decimal (if s > 0), xs:integer (if s <=0)
NVARCHAR2
xs:string
RAW
xs:hexBinary
ROWID
xs:string
UROWID
xs:string

1Pushed down in project list only.

2Does not support fractional seconds.

3Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).

Additionally, the following XQuery data types can be passed as parameters or returned by pushed functions:

Function and Operator Pushdown

Table G-10 lists functions and operators that are pushed down. See “fn-bea:sql-like” on page 2-26 for details about two-argument and three-argument versions of the fn-bea:sql-like() function.

Table G-10 Oracle 8.1.x Function and Operator Pushdown  
Group
Functions and operators
Logical operators
and, or, not
Numeric arithmetic1
+, -, *, div, idiv, mod
Numeric comparisons1
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge
Numeric functions
abs, ceiling, floor, round
String comparisons2
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge
String functions
concat, upper-case3, lower-case3, substring(2,3)3, string-length4, contains5, starts-with5, ends-with5, fn-bea:sql-like(2,3), fn-bea:trim, fn-bea:trim-left, fn-bea:trim-right, fn-bea:repeat, fn-bea:pad-left, fn-bea:pad-right
Datetime comparisons
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xdt:yearMonthDuration
Datetime functions
year-from-dateTime, year-from-date, years-from-duration, month-from-dateTime, month-from-date, months-from-duration, day-from-dateTime, day-from-date, days-from-duration, hours-from-dateTime, minutes-from-dateTime, seconds-from-dateTime, fn-bea:date-from-dateTime
Datetime arithmetic
op:add-yearMonthDurations, op:subtract-yearMonthDurations, op:multiply-yearMonthDuration, op:divide-yearMonthDuration, subtract-dateTimes-yielding-yearMonthDuration, op:add-yearMonthDuration-to-dateTime, op:subtract-yearMonthDuration-from-dateTime, subtract-dates-yielding-yearMonthDuration, op:add-yearMonthDuration-to-date, op:subtract-yearMonthDuration-from-date
Aggregate
min, max, sum, avg, count, count(distinct-values)
Other
empty, exists, subsequence6

1For all numeric types.

2Arguments must be of SQL data type CHAR, NCHAR, NVARCHAR2, or VARCHAR2.

3Empty input (NULL) handling deviates from XQuery semantics—returns empty sequence (instead of empty string).

4Argument must be data type CHAR, NCHAR, NVARCHAR2, or VARCHAR2.

5Second argument must be data type CHAR, NCHAR, NVARCHAR2, or VARCHAR2.

6Both two- and three-argument variants of fn:subsequence() are supported without restriction.

Cast Operation Pushdown

Table G-11 lists supported cast operations.

Table G-11 Oracle 8.1.x Cast Operation Pushdown  
Source XQuery Type
Target XQuery Type
numeric
xs:string
numeric
xs:decimal
numeric
xs:integer
numeric
xs:float
numeric
xs:double
xs:string
xs:decimal1
xs:string
xs:integer1
xs:string
xs:float1
xs:string
xs:double1
xs:dateTime
xs:date
xs:date
xs:dateTime

1Source data type must be CHAR, NCHAR, NVARCHAR2, or VARCHAR2.

Other SQL Generation Capabilities

Table G-12 lists common query patterns that can be pushed down. See “Common Query Patterns” for details.

Table G-12 Oracle 8.1.x Other SQL Generation Capabilities  
Feature
Description
If-then-else
yes
Inner joins
yes, SQL-89 syntax
Outer joins
yes, Oracle proprietary syntax
Semi joins, Anti semi joins
yes
Order by
yes
Order by: Empty order (NULL order)
dynamic, no restriction on order by pushdown
Order by: Aggregate function in ordering expression
yes
Group by
yes
Distinct pattern
yes
Trivial aggregate pattern
yes (using GROUP BY constant)
Direct SQL composition
yes

 


Oracle 9.x, 10.x

The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Oracle 9.x (Oracle 9i) and Oracle 10.x (Oracle 10g). Note that Oracle treats empty strings as NULLs, which deviates from XQuery semantics and may lead to unexpected results for expressions that are pushed down.

Data Type Mapping

Table G-13 lists supported data type mappings for Oracle 9.x and 10.x.

Table G-13 Oracle 9.x, 10.x Data Type Mapping  
Oracle 9 Data Type
XQuery Type
BFILE
not supported
BLOB
xs:hexBinary
CHAR
xs:string
CLOB1
xs:string
DATE
xs:dateTime2
FLOAT
xs:double
INTERVAL DAY TO SECOND
xdt:dayTimeDuration
INTERVAL YEAR TO MONTH
xdt:yearMonthDuration
LONG1
xs:string
LONG RAW
xs:hexBinary
NCHAR
xs:string
NCLOB1
xs:string
NUMBER
xs:double
NUMBER(p,s)
xs:decimal (if s > 0), xs:integer (if s <=0)
NVARCHAR2
xs:string
RAW
xs:hexBinary
ROWID
xs:string
TIMESTAMP
xs:dateTime3
TIMESTAMP WITH LOCAL TIMEZONE
xs:dateTime
TIMESTAMP WITH TIMEZONE
xs:dateTime
VARCHAR2
xs:string
UROWID
xs:string

1Pushed down in project list only.

2When SDO stores xs:dateTime value in Oracle DATE type, it is converted to local time zone and fractional seconds are truncated due to DATE limitations. See “Date and Time Data Type Differences: Timezones and Time Precision” on page 3-6 for more information.

3XQuery engine maps XQuery xs:dateTime to either TIMESTAMP or TIMESTAMP WITH TIMEZONE data type, depending on presence of timezone information. Storing xs:dateTime using SDO may result in loss of precision for fractional seconds, depending on the SQL type definition.

Additionally, these XQuery data types can be passed as parameters or returned by pushed functions:

Function and Operator Pushdown

Table G-14 lists functions and operators that are pushed down to Oracle 9.x and 10.x. See “fn-bea:sql-like” on page 2-26 for details about two-argument and three-argument versions of the fn-bea:sql-like() function.

Table G-14 Oracle 9.x, 10.x Function and Operator Pushdown  
Group
Functions and Operators
Logical operators
and, or, not
Numeric arithmetic1
+, -, *, div, idiv, mod
Numeric comparisons1
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge
Numeric functions
abs, ceiling, floor, round
String comparisons2
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge
String functions
concat, upper-case3, lower-case3, substring(2,3)3, string-length4, contains5, starts-with5, ends-with5, fn-bea:sql-like(2,3), fn-bea:trim, fn-bea:trim-left, fn-bea:trim-right, fn-bea:repeat, fn-bea:pad-left, fn-bea:pad-right
Datetime comparisons
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xdt:yearMonthDuration, xdt:dayTimeDuration
Datetime functions
year-from-dateTime, year-from-date, years-from-duration, month-from-dateTime, month-from-date, months-from-duration, day-from-dateTime, day-from-date, days-from-duration, hours-from-dateTime, hours-from-duration, minutes-from-dateTime, minutes-from-duration, seconds-from-dateTime, seconds-from-duration, fn-bea:date-from-dateTime
Datetime arithmetic
op:add-yearMonthDurations, op:add-dayTimeDurations, op:subtract-yearMonthDurations, op:subtract-dayTimeDurations, op:multiply-yearMonthDuration, op:multiply-dayTimeDuration, op:divide-yearMonthDuration, op:divide-dayTimeDuration, subtract-dateTimes-yielding-yearMonthDuration, subtract-dateTimes-yielding-dayTimeDuration, op:add-yearMonthDuration-to-dateTime, op:add-dayTimeDuration-to-dateTime, op:subtract-yearMonthDuration-from-dateTime, op:subtract-dayTimeDuration-from-dateTime, subtract-dates-yielding-yearMonthDuration, subtract-dates-yielding-dayTimeDuration, op:add-yearMonthDuration-to-date, op:add-dayTimeDuration-to-date, op:subtract-yearMonthDuration-from-date, op:subtract-dayTimeDuration-from-date
Aggregate
min, max, sum, avg, count, count(distinct-values)
Other
empty, exists, subsequence6

1For all numeric types

2Arguments must be of SQL type (N)CHAR or (N)VARCHAR2

3Empty input (NULL) handling deviates from XQuery semantics—returns empty sequence (instead of empty string).

4Argument must be CHAR, CLOB, NCHAR, NVARCHAR2, or VARCHAR2 data type.

5Second argument must be CHAR, NCHAR, NVARCHAR2, or VARCHAR2 data type.

6Both two- and three-argument variants of fn:subsequence() are supported without restriction.

Cast Operation Pushdown

Table G-15 lists cast operations that can be pushed down.

Table G-15 Oracle 9.x, 10.x Cast Operation
Source XQuery Type
Target XQuery Type
numeric
xs:string
numeric
xs:decimal
numeric
xs:integer
numeric
xs:float
numeric
xs:double
xs:string
xs:decimal1
xs:string
xs:integer
xs:string
xs:float
xs:string
xs:double
xs:dateTime
xs:date
xs:date
xs:dateTime2

1Source SQL type must be CHAR, NCHAR, VARCHAR2, or NVARCHAR2.

2Source SQL type must be DATE or TIMESTAMP to achieve this mapping.

Other SQL Generation Capabilities

Table G-16 lists common query patterns that can be pushed down. (See “Common Query Patterns” for details.)

Table G-16 Oracle 9.x, 10.x Other SQL Generation Capabilities
Feature
Description
If-then-else
yes
Inner joins
yes, SQL-92 syntax
Outer joins
yes, SQL-92 syntax
Semi joins, Anti semi joins
yes
Order by
yes
Order by: Empty order (NULL order)
dynamic, no restriction on order by pushdown
Order by: Aggregate function in ordering expression
yes
Group by
yes
Distinct pattern
yes
Trivial aggregate pattern pushdown
yes (using GROUP BY constant)
Direct SQL composition
yes

 


Sybase 12.5.2 (and higher)

The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Sybase 12.5.2 (and higher).

As you read through the tables in this section, be aware that Sybase deviates from XQuery semantics (which ignores empty strings) and treats empty strings as a single-space string.

Data Type Mapping

Table G-17 lists supported data type mappings for Sybase 12.5.2.

Table G-17 Sybase 12.5.2 Data Type Mapping  
Sybase Data Type
XQuery Type
BINARY
xs:hexBinary
BIT
xs:boolean
CHAR
xs:string
DATE
xs:date
DATETIME1
xs:dateTime2
DECIMAL(p,s)3 (NUMERIC)
xs:decimal (if s > 0), xs:integer (if s == 0)
DOUBLE PRECISION
xs:double
FLOAT
xs:double
IMAGE
xs:hexBinary
INT (INTEGER)
xs:int
MONEY
xs:decimal
NCHAR
xs:string
NVARCHAR
xs:string
REAL
xs:float
SMALLDATETIME4
xs:dateTime
SMALLINT
xs:short
SMALLMONEY
xs:decimal
SYSNAME
xs:string
TEXT5
xs:string
TIME
xs:time
TINYINT
xs:short
VARBINARY
xs:hexBinary
VARCHAR
xs:string

1Supports fractional seconds up to 3 digits (milliseconds) precision; no timezone information.

2Values converted to local time zone (timezone information removed) and fractional seconds truncated to milliseconds due to DATETIME limitations. See “Date and Time Data Type Differences: Timezones and Time Precision” on page 3-6 for more information.

3Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).

4Accurate to 1 minute.

5Expressions returning text are pushed down in the project list only.

Additionally, the following data types can be passed as parameters or returned by pushed functions:

See “Datetime arithmetic” in Table  for details.

Function and Operator Pushdown

Table G-18 lists functions and operators that are pushed down to base RDBMSs. (See “fn-bea:sql-like” on page 2-26 for details about two-argument and three-argument versions of the fn-bea:sql-like( ) function.)

Table G-18 Sybase 12.5.2 Function and Operator Pushdown  
Group
Functions and operators
Logical operators
and, or, not
Numeric arithmetic
+, -, *, div 1
idiv2
mod3
Numeric comparisons1
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge
Numeric functions
abs, ceiling, floor, round
String comparisons4
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge
String functions
concat5, upper-case, lower-case, substring(2,3), string-length, contains6, starts-with6, ends-with6, fn-bea:sql-like(2,3), fn-bea:trim, fn-bea:trim-left, fn-bea:trim-right, fn-bea:repeat, fn-bea:pad-left, fn-bea:pad-right
Datetime comparisons
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xs:time, xdt:yearMonthDuration, xdt:dayTimeDuration
Datetime functions
year-from-dateTime, year-from-date, years-from-duration, month-from-dateTime, month-from-date, months-from-duration, day-from-dateTime, day-from-date, days-from-duration, hours-from-dateTime, hours-from-time, hours-from-duration, minutes-from-dateTime, minutes-from-time, minutes-from-duration, seconds-from-dateTime, seconds-from-time, seconds-from-duration, fn-bea:date-from-dateTime, fn-bea:time-from-dateTime
Datetime arithmetic
op:add-yearMonthDurations, op:subtract-yearMonthDurations, op:multiply-yearMonthDuration, op:divide-yearMonthDuration, op:add-dayTimeDurations, op:subtract-dayTimeDurations, op:multiply-dayTimeDuration, op:divide-dayTimeDuration, op:add-yearMonthDuration-to-dateTime, op:add-yearMonthDuration-to-date, op:subtract-yearMonthDuration-from-dateTime, op:subtract-yearMonthDuration-from-date, op:add-dayTimeDuration-to-dateTime, op:add-dayTimeDuration-to-date, op:subtract-dayTimeDuration-from-dateTime, op:subtract-dayTimeDuration-from-date, fn:subtract-dateTimes-yielding-yearMonthDuration, fn:subtract-dates-yielding-yearMonthDuration, fn:subtract-dateTimes-yielding-dayTimeDuration, fn:subtract-dates-yielding-dayTimeDuration
Aggregate
min, max, sum, avg, count, count(distinct-values)
Other
empty, exists

1All numeric types (+, -, *, div operators are pushed down for all numeric types).

2xs:decimal (and subtypes) only

3xs:integer (and subtypes) only

4Arguments must be SQL data type CHAR, NCHAR, NVARCHAR, or VARCHAR.

5Each argument must be SQL data type CHAR, NCHAR, NVARCHAR, or VARCHAR.

6Second argument must be constant or SQL parameter.

Cast Operation Pushdown

The Table G-19 lists supported cast operations.

Table G-19 Sybase 12.5.2 Cast Operation Pushdown
Source XQuery Type
Target XQuery Type
numeric
xs:double
numeric
xs:float
numeric
xs:int
numeric
xs:short
numeric
xs:string
xs:decimal (and subtypes)
xs:integer
xs:integer (and subtypes)
xs:decimal
xs:string
xs:double1
xs:string
xs:float
xs:string
xs:int
xs:string
xs:integer
xs:string
xs:short
xs:dateTime
xs:date
xs:dateTime
xs:time
1Source SQL type must be (N)CHAR or (N)VARCHAR

Other SQL Generation Capabilities

Table G-20 lists common query patterns that can be pushed down. See “Common Query Patterns” for details.

Table G-20 Sybase 12.5.2 Other SQL Generation Capabilities
Feature
Description
If-then-else
yes
Inner joins
yes, SQL-92 syntax
Outer joins
yes, SQL-92 syntax
Semi joins, Anti semi joins
yes
Order by
yes
Order by: Empty order (NULL order)
fixed (always sorts NULLs low). Order-bys with "empty greatest" modifier are not pushed down.
Order by: Aggregate function in ordering expression
yes
Group by
yes
Distinct pattern
yes
Trivial aggregate pattern
yes (using subquery)
Direct SQL composition
yes

 


PointBase 5.1

The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for PointBase 5.1.

Data Type Mapping

Table G-21 lists supported data type mappings for PointBase 5.1.

Table G-21 PointBase 5.1 Data Type Mapping
PointBase Data Type
XQuery Type
BIGINT
xs:long
BLOB
xs:hexBinary
BOOLEAN
xs:boolean
CHAR (CHARACTER)
xs:string
CLOB
xs:string
DATE
xs:date
DECIMAL(p,s)1 (NUMERIC)
xs:decimal (if s > 0), xs:integer (if s == 0)
DOUBLE PRECISION
xs:double
FLOAT
xs:double
INTEGER (INT)
xs:int
SMALLINT
xs:short
REAL
xs:float
TIME
xs:time
TIMESTAMP
xs:dateTime
VARCHAR
xs:string

1Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).

Function and Operator Pushdown

Table G-22 lists functions and operators that are pushed down to PointBase. See “fn-bea:sql-like” on page 2-26 for details about two-argument and three-argument versions of the fn-bea:sql-like() function.

Table G-22 PointBase 5.1 Function and Operator Pushdown
Group
Functions and operators
Logical operators
and, or, not
Numeric arithmetic1
+, -, *, div, idiv
Numeric comparisons1
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge
String comparisons2
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge
String functions
concat,upper-case, lower-case, substring(2,3), string-length, contains3, starts-with3, ends-with3, fn-bea:sql-like(2,3) fn-bea:trim, fn-bea:trim-left, fn-bea:trim-right
Datetime comparisons
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xs:time
Datetime functions
year-from-dateTime, year-from-date, month-from-dateTime, month-from-date, day-from-dateTime, day-from-date, hours-from-dateTime, hours-from-time, minutes-from-dateTime, minutes-from-time, seconds-from-dateTime, seconds-from-time, fn-bea:date-from-dateTime
Aggregate
min, max, sum, avg, count, count(distinct-values)
Other
empty, exists

1All numeric types

2CHAR or VARCHAR SQL data types only for arguments

3Second argument must be constant or parameter.

Cast Operation Pushdown

Table G-19 lists supported cast operations.

Table G-23 PointBase 5.1 Cast Operation Pushdown
Source XQuery Type
Target XQuery Type
numeric
xs:decimal
numeric
xs:double
numeric
xs:float
numeric
xs:int
numeric
xs:short
numeric
xs:string
xs:integer and its subtypes
xs:integer
xs:integer and its subtypes
xs:long
xs:string
xs:decimal1
xs:string
xs:double1
xs:string
xs:float1
xs:string
xs:integer1
xs:string
xs:long1
xs:string
xs:int1
xs:string
xs:short1
xs:dateTime
xs:date

1Source SQL data type must be CHAR or VARCHAR

Other SQL Generation Capabilities

Table G-24 lists common query patterns that can be pushed down. (See “Common Query Patterns” for details.)

Table G-24 PointBase 5.1 Other SQL Generation Capabilities
Feature
Description
If-then-else
no
Inner joins
yes, SQL-92 syntax
Outer joins
yes (partially), SQL-92 syntax. Only simple outer joins are pushed, the ones that require subquery don't (e.g. when right branch has a where clause)
Semi joins, Anti semi joins
yes
Order by
yes
Order by: Empty order (NULL order)
fixed (always sorts NULLs low). Order-bys with "empty greatest" modifier are not pushed down.
Order by: Aggregate function in ordering expression
no
Group by
yes (Group by function expression is not supported, only group by column is pushed
Distinct pattern
yes
Trivial aggregate pattern pushdown
no
Direct SQL composition
no

 


Teradata V2R5 (and higher)

The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Teradata V2R5 (and higher).

Data Type Mapping

Table G-25 lists supported data type mappings for Teradata V2R5.

Table G-25 Teradata V2R5 Data Type Mapping
Teradata Data Type
XQuery Type
BYTE
xs:hexBinary
BYTEINT
xs:short
CHAR
xs:string
DATE
xs:date
DECIMAL(p,s) (NUMERIC)
xs:decimal (if s > 0),
xs:integer (if s == 0)
FLOAT (REAL, DOUBLE PRECISION)
xs:double
INTEGER
xs:int
LONG VARCHAR
xs:string
SMALLINT
xs:short
TIME
xs:time
TIMESTAMP
xs:dateTime
VARBYTE
xs:hexBinary
VARCHAR
xs:string

Function and Operator Pushdown

Table G-26 lists functions and operators that are pushed down to Teradata. See “fn-bea:sql-like” on page 2-26 for details about two-argument and three-argument versions of the fn-bea:sql-like() function.

Table G-26 Teradata V2R5 Function and Operator Pushdown
Group
Functions and operators
Logical operators
and, or, not
Numeric arithmetic
+, -, * 1
div2
Numeric comparisons1
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge
String comparisons3
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge
String functions
concat3, upper-case4, lower-case4, contains5, starts-with5, ends-with5, fn-bea:sql-like(2,3)5
Datetime comparisons
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xs:time
Aggregate
min, max, sum, avg, count, count(distinct-values)
Other
empty, exists

1All numeric types

2Only xs:decimal, xs:float, and xs:double

3CHAR or VARCHAR SQL data types only for all arguments

4CHAR or VARCHAR SQL data type only for first argument

5First argument must be CHAR or VARCHAR SQL data type, second argument must be a constant or parameter

Cast Operation Pushdown

Cast operations are not pushed down.

Other SQL Generation Capabilities

Table G-27 lists common query patterns that can be pushed down. (See “Common Query Patterns” for details.)

Table G-27 Teradata V2R5 Other SQL Generation Capabilities
Feature
Description
Inner joins
yes, SQL-92 syntax
Outer joins
yes
Semi joins, Anti semi joins
yes
Order by
yes
Order by: Empty order (NULL order)
fixed (always sorts NULLs low). Order-bys with "empty greatest" modifier are not pushed down.
Order by: Aggregate function in ordering expression
no
Group by
yes
Distinct pattern
yes
Trivial aggregate pattern pushdown
yes (using GROUP BY constant)
If-then-else pushdown
yes
Subsequence pushdown
yes
SQL Exit query composition (pushdown on top of SQL Exit)
yes
Runtime connection management
no connection sharing

 


Base (Generic) RDBMS Support

Each JDBC drivers provide information about inherent properties and capabilities of the RDBMS with which it is associated. During the metadata import process, Oracle Data Service Integrator queries a configured data source’s JDBC driver for basic properties and capabilities information. Much of the information obtained is stored in the metadata section of the data service definition file (.ds). See “ Understanding Data Service Annotations” in the Data Services Developer’s Guide for more information.

Database Capabilities Information

The database capabilities listed in Table G-28 are obtained from the operative JDBC driver and stored as properties in the .ds (data service) definition file.

Table G-28 Database Properties Derived from the JDBC Driver
Property
Description
Possible Values
supportsSchemasInDataManipulation
Boolean that identifies whether SQL statements can include schema names
true, false
supportsCatalogsInDataManipulation
Boolean that identifies whether database catalogs can be addressed by SQL
true, false
supportsLikeEscapeClause
Boolean that identifies if the database supports ESCAPE clause in LIKE expression
true, false
nullSortOrder
Order in which NULLs are sorted
low, high, unknown
identifierQuote
String used as delimiter to denote (offset) identifier labels
String value (can be empty)
catalogSeparator
String used as delimiter (separator) between catalog (or schema) and table name
String value

The Oracle Data Service Integrator XQuery engine typically quotes the names (identifiers) of object names to properly handle any special characters. The identifierQuote property (see Table ) is obtained from the JDBC driver. However, different RDBMSs may use different identifiers for different database object names:

If necessary, you can manually override the identifier quote property for each type of identifier (see Table ).

Typically, the identifierQuote property obtained from the JDBC driver is used. However, if the specific quote property is available and the RDBMS uses it, you can modify the annotation settings in the .ds file (see “ Understanding Data Service Annotations” in the Data Services Developer’s Guide for more information about these properties). The XQuery engine (metadata importer sub-system) uses the specific quote property (see Table G-29) if it is available, otherwise, it uses the “identifierQuote” property provided by the JDBC driver.

The only exception to this rule is for Sybase versions below Sybase 12.5.2, which is treated as a base platform. Sybase does not use quotes for catalogs even though JDBC drivers return double quote ('"') for “identifierQuote” property. The XQuery engine accommodates this mismatch by automatically setting “catalogQuote” property to the empty string.

Table G-29 Optional Quote Properties for Database Objects
Property
Description
Possible Values
catalogQuote
Special character used as quote to denote name of catalog
string
schemaQuote
Special character used as quote to denote name of schema
string
tableQuote
Special character used as quote to denote name of table
string
columnQuote
Special character used as quote to denote name of column
string

Data Type Mapping

When mapping SQL to XQuery datatypes, the XQuery engine first checks the JDBC typecode. If the typecode has a corresponding XQuery type, Oracle Data Service Integrator uses the matching native type name. If no matching typecode or type name is available, the column is ignored. Table G-30 shows this mapping.

Table G-30 Base Platform Data Type Mapping (JDBC<– >XQuery Equivalents)  
JDBC Data Type
Typecode
XQuery Data Type
BIGINT
-5
xs:long
BINARY
-2
xs:string
BIT
-7
xs:boolean
BLOB
2004
xs:hexBinary
BOOLEAN
16
xs:boolean
CHAR
1
xs:string
CLOB1
2005
xs:string
DATE
91
xs:date2
DECIMAL (p,s)3
3
xs:decimal (if s > 0), xs:integer (if s =0)
DOUBLE
8
xs:double
FLOAT
6
xs:double
INTEGER
4
xs:int
LONGVARBINARY
-4
xs:hexBinary
LONGVARCHAR1
-1
xs:string
NUMERIC (p,s)3
2
xs:decimal (if s > 0), xs:integer (if s =0)
REAL
7
xs:float
SMALLINT
5
xs:short
TIME4
92
xs:time4
TIMESTAMP4
93
xs:dateTime2
TINYINT
-6
xs:short
VARBINARY
-3
xs:hexBinary
VARCHAR
12
xs:string
OTHER
1111
Oracle Data Service Integrator uses native data type name to map to an appropriate XQuery data type.
Other vendor-specific JDBC type codes

1Pushed down in project list only.

2Values converted to local time zone (timezone information removed) due to DATE limitations. See “Date and Time Data Type Differences: Timezones and Time Precision” on page 3-6 for more information.

3Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).

4Precision of underlying RDBMS determines the precision of TIME data type and how much truncation, if any, will occur in translating xs:time to TIME.

Table G-31 lists functions and operators that are pushed down to base RDBMSs. See “fn-bea:sql-like” on page 2-26 for details about two-argument and three-argument versions of the fn-bea:sql-like() function.

Table G-31 Base Platform Functions and Operators  
Group
Functions and Operators
Logical operators
and, or, not
Numeric arithmetic
+, -, *1
div2
Numeric comparisons1
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge
String comparisons3
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge
String functions
contains4, starts-with4, ends-with4, fn-bea:sql-like(2), fn-bea:sql-like(3),4 upper-case, lower-case
Datetime comparisons
=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xs:time
Other
empty, exists

1All numeric types

2Support for xs:decimal, xs:float, and xs:double data types only.

3Arguments must be CHAR or VARCHAR SQL data types.

4First argument must be SQL data type CHAR or VARCHAR; second argument must be a constant or parameter; and RDBMS must support LIKE (with ESCAPE) clause.

Cast Operation Pushdown

For base RDBMS, cast operations are not pushed down.

Other SQL Generation Capabilities

Table G-32 displays other SQL Pushdown capabilities, as discussed in “Common Query Patterns” on page 3-15.

Table G-32 Base Platform SQL Generation Capabilities  
Query
Supported
If-Then-Else
no
Inner joins
yes (SQL-89 syntax)
Outer joins
no
Semi-joins, Anti-semi-joins
no
Order by
yes
Order by: Empty (NULL) order supported
Database-dependent
Order by: Aggregate function in ordering expression
no
Group by
yes (by column only)
Distinct pattern
yes
Trivial aggregate pattern
no
Direct SQL composition
no


  Back to Top       Previous  Next