# XQuery and XQSE Developer’s Guide

# 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:

- Core RDBMS Support:

- IBM DB2/NT 8 (and higher)
- Microsoft SQL Server 2000 (and higher)
- Oracle 8.1.x
- Oracle 9.x, 10.x
- Sybase 12.5.2 (and higher)
- PointBase 5.1
- Teradata V2R5 (and higher)

- Base (Generic) RDBMS Support

Each section that follows includes information about:

- Database Capabilities Information
- Native RDBMS Data Type Support and XQuery Mappings
- Function and Operator Pushdown
- Cast Operation Pushdown
- Other SQL Generation Capabilities (including join pushdown support and SQL syntax for joins)

## 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:

- xs:date (see Table G-6 for functions and operators that use xs:date). When xs:date is sent to the database, it is converted to local time zone. See “Date and Time Data Type Differences: Timezones and Time Precision” on page 3-6 for more information.
- xdt:dayTimeDuration (see “Datetime Arithmetic” functions in Table G-6 for details).
- xdt:yearMonthDuration (see “Datetime Arithmetic” functions in Table G-6 for details).

### 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:

- xs:date (see Table G-10 for functions and operators that use xs:date)
- xdt:yearMonthDuration (see “Datetime Arithmetic” in Table G-10 for details)
- xs:integer subtypes (see “Numeric ...” functions and operators in Table G-10 for details)

### 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:

- xs:date (see Table G-14 for functions and operators that use xs:date)
- xs:integer subtypes (see “Numeric ...” functions and operators in Table G-14 for details)

### 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:

- xdt:dayTimeDuration
- xdt:yearMonthDuration

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:

- catalogs
- schemas
- tables
- columns

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