Getting error for the case statement while using nvl with DB2 DB

[kvchow]

Hi
I am using the below case statement in my column
case
when L.Stat=100 then
'NC'
When (L.stat = 100 and nvl(L.exp_on_date - L.acq_on_date,1) > nvl(M.VAL_TI_Date,1)-1  ) THEN 'COMP'
END
I am getting the below error
UDA-SQL-0219 The function "nvl" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.RSV-SRV-0042
Cognos 10.1 &DB2 environment

Any help

Thanks,
Kvchow

[MFGF]

Hi
I am using the below case statement in my column
case
when L.Stat=100 then
'NC'
When (L.stat = 100 and nvl(L.exp_on_date - L.acq_on_date,1) > nvl(M.VAL_TI_Date,1)-1  ) THEN 'COMP'
END
I am getting the below error
UDA-SQL-0219 The function "nvl" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.RSV-SRV-0042
Cognos 10.1 &DB2 environment

Any help

Thanks,
Kvchow

Hi,

nvl is a native database function, but it appears your calculation is being performed locally on the Cognos server, so only Cognos functions are available to you. Have you tried using the coalesce() function instead?

MF.

[kvchow]

Hi MF,

Thanks for your  response..

I haven't tried coalesce(),i will try and let you know

Thanks,
kvchow

[kvchow]

Hi
Now this time(after using the coalesce) i am getting diff error..ie data types mismatching

UDA-SQL-0460 A general exception has occurred during local processing.UDA-EE-0094 The operation "equal" is invalid for the following combination of data types: "nvarchar" and "integer
i checked the data types for all and M.VAL_TI_Date this column datatype is defined as decimal in fm

these 2 column data types are defined as date time(L.exp_on_date - L.acq_on_date)

thanks,

[MFGF]

Hi
Now this time(after using the coalesce) i am getting diff error..ie data types mismatching

UDA-SQL-0460 A general exception has occurred during local processing.UDA-EE-0094 The operation "equal" is invalid for the following combination of data types: "nvarchar" and "integer
i checked the data types for all and M.VAL_TI_Date this column datatype is defined as decimal in fm

these 2 column data types are defined as date time(L.exp_on_date - L.acq_on_date)

thanks,

Hi,

So what expression have you ended up with? Are you still subtracting one date from another? This will deliver an interval result. Would you use the _days_between() function instead?

MF.

[kvchow]

Hi MF,

I tried the days_Between but that not working in my case,Because if that date  substaraction is null then it will return 1,if 1 >0 then status is 'COMP'

That is the scenarion in my case statement

thanks

[MFGF]

Hi MF,

I tried the days_Between but that not working in my case,Because if that date  substaraction is null then it will return 1,if 1 >0 then status is 'COMP'

That is the scenarion in my case statement

thanks

Hi,

That still doesn't tell us what expression you have ended up with?? Nobody but you can see the expression - you're going to have to tell us what it is or we would be guessing...

You would typically use the coalesce() function around each date in the _days_between() function so you would not end up with a null date??

MF.