The Generic_ODBC mappings.xml file contains empty elements for functions and operators. You customize function mappings by finding the appropriate element, such as BooleanOpMapper or NumericFuncMapper, and adding a FunctionMapping child element. The FunctionMapping element contains the attributes listed in
Table 11-1.
The Integration service calculates the return data type for a function. You cannot calculate the return data type manually in a reliable manner. For example, when mapping the CAST function, do not use CAST ($P0 AS NVARCHAR). Instead, use CAST ($P0 AS $R). The Integration service determines the correct data type. When you use the $R syntax, you must explicitly declare the name of the database data type in the DataTypeMapper element, otherwise the ODBC name, for example SQL_VARCHAR, is used.
When you test your queries, you discover that the default mapping for the Actuate SQL POWER function returns an error. The Integration service uses the ODBC escape sequence {FN POWER ($P0, $P1)} as the default mapping. However, your JDBC driver does not have an implementation for the escape sequence and thus cannot rewrite the expression. Therefore, you must map the POWER function.
After checking your database documentation, you determine that the database has a function called POWER and that it takes two arguments. You compare this to the Actuate SQL POWER prototypes:
For another example, refer to the mappings.xml file for Oracle, which requires a very different mapping for the POWER function. To map POWER, you use the OperandTypes attribute and define three different mappings:
The Actuate SQL DATEDIFF function uses a prototype that enables you to provide a date part such as yyyy. Thus, you can use the same function to do date subtraction for years, months, days, etc.:
When you test your queries you discover there is a problem with the default mapping for the DATEDIFF function with date part yyyy. The driver is mapping the DATEDIFF function to the YEARS_BETWEEN function on the database. The query gives incorrect results because the year is consistently off by one.