Cast to date in DB2 database on IBM ISeries (AS/400).

Technote (troubleshooting)


Problem(Abstract)

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'.

[IBM][Client
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)

Cross Reference information
Segment Product Component Platform Version Edition
Business Analytics Cognos 8 Business Intelligence Framework Manager
Business Analytics Cognos 8 Business Intelligence Report Studio
Business Analytics Cognos ReportNet ReportNet

Historical Number

1010917

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Cognos ReportNet
ReportNet

Software version:

Cognos 8 BI Framework Manager 8.1, Cognos 8 BI Report Studio 8.1, Cognos ReportNet 1.1MR2, Cognos ReportNet 1.1MR3

Operating system(s):

AIX, All, Solaris, Win, Windows

Reference #:

1335628

Modified date:

2007-04-04

Translate my page

Machine Translation

Content navigation