When importing metadata into Framework Manager all date fields have the data type decimal, decimal 8. When trying to convert the data to dates, a Client Access ODBC error is returned. When using a vendor specific DB2 function to convert to a date an error may not occur, but all the date are initialized. For example, 20050207 becomes 0/0/0000.
Cast QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-120'. UDA-SQL-0458 PREPARE failed because the query requires local processing of the data. The option to allow local processing has not been enabled. UDA-SQL-0481 An expression or operator (cast) is not supported by the database. This operation requires local processing of the data. Cast_date (vendor specific DB2 function):
QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'.
Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0204 - CAST_DATE in *LIBL type *N not found.
[Microsoft][ODBC Driver Manager] Function sequence error
Resolving the problem
DB2 on iSeries does not have a number-to-date conversion, and other formulas described above does not work either. The only way casting to a date is supported is by the following formula (This formula will also work for other databases):
cast(substring(cast([DB].[O].[ODate] as varchar( 8 )),1,4)+'-'+substring(cast([DB].[O].[ODate] as varchar( 8 )),5,2)+'-'+
substring(cast([DB].[O].[ODate] as varchar( 8 )),7,2) as date)
|Business Analytics||Cognos 8 Business Intelligence||Framework Manager|
|Business Analytics||Cognos 8 Business Intelligence||Report Studio|
|Business Analytics||Cognos ReportNet||ReportNet|