DB2 SQL vs. Native SQL

[VonDobsky]

Hi...ok i will be more specific than last post..

I am having issues with some Vendor specific functions with DB2 that spits out errors due it not being Native sql.
for instance,

cast_date(cast_integer(substring([Z010U].[CFCSCCHLINEHIST].[ACTIVITYDATE],5,2)),cast_integer(substring([Z010U].[CFCSCCHLINEHIST].[ACTIVITYDATE],7,2)),cast_integer(substring([Z010U].[CFCSCCHLINEHIST].[ACTIVITYDATE],1,4)))

What's wrong here? it validates, but won't run properly.

[blom0344]

AFAIK cast_date and cast_integer are cognos functions. If you use this as native SQL then the DB2 UDA will not be able to compile these. At report level the validation will succeed as the expression is a valid Cognos expression..

[angela]

Try this:
     date(substring(trim(char([Z010U].[CFCSCCHLINEHIST].[ACTIVITYDATE])), 5, 2)||
            '-' ||
            substring(trim(char([Z010U].[CFCSCCHLINEHIST].[ACTIVITYDATE])), 7, 2) ||
            '-' ||
            substring(trim(char([Z010U].[CFCSCCHLINEHIST].[ACTIVITYDATE])), 1, 4))
           ) as ACTIVITYDATE


You may have to play with it a bit.  And don't use Cognos SQL.