Errors related to mismatched SQL and Java data types

A table column might use a vendor data type which the JDBC driver does not directly support and attempts to return as another data type such as VARCHAR.

For example, a table includes a column of type ARRAY and a column of type STRUCT which the JDBC driver describes as a VARCHAR. Effectively, to IBM® Cognos® Analytics, those columns and VARCHAR data types and any operation the vendor supports involving a VARCHAR are supported. Cognos Analytics may generate a SQL statement including operations, such as COUNT, DISTINCT or ORDER BY referencing those columns. The statement may not execute if the vendor does not support those operations on the data type of the column (for example, ARRAY).

These types of errors might occur when both of these conditions are true:

• You import schema metadata from a database, for example, to create data modules,
• The options to retrieve sample data are turned on.

For more information, see Loading metadata. Similar errors might occur when you create and test model query subjects in Framework Manager.

To avoid errors that are related to mismatched data types, try these solutions:

• Read the related database vendor documentation to find out how a JDBC driver defines the SQL data types that are supported by the database.
• Define in-database views or expressions that convert the mismatched data types into types that are recognized by Cognos Analytics.

  For more information, see Unknown types.