8.3>> 8.4 ... reports with Nvl and Extract functions are not working

[hrk551]

Recently we moved from 8.3 to 8.4, and Oracle 10g to 11g. 2 reports are not working in this process

1 report error:
1) 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.

second report error:
2)UDA-SQL-0107 A general exception has occurred during the operation "prepare".
ORA-30076: invalid extract field for extract source

Thanx in advfor your help

[Suraj]

What is the expression in data item?
I will test and see if it works here.

[hrk551]

1) extract(day, [DISB Date] - {sysdate} )

2)nvl([SR Loan],0)+nvl([Orig Equity],0)+nvl([Guarantee Amt],0)+nvl([Sub Loan],0)+nvl([Quasi Equity],0)+nvl([SCF Amount],0)

[blom0344]

1) extract(day, [DISB Date] - {sysdate} )

What is this supposed to yield? You are using an Oracle expression within a Cognos one. Why not use current_date to build a fully Cognos expression?

[Suraj]

1) extract(day, [DISB Date] - {sysdate} )

Okay, I'm not sure as well on what you are trying to return.

Just [DISB Date] - {sysdate}   gives you days difference. Why do you have to extract day after that?
You extract day from a date not from a difference between dates.
You can either use [DISB Date] - {sysdate}    or extract(day, [DISB Date] ) - extract(day, {sysdate} )

Haven't had chance to test 2).

[rthomas]

We had the same problem on a few reports and resolved by using the coalesce function.

Hope this works.  Good luck

RT

[hrk551]

Suraj --- what you said worked for me. I dont know how the old condition worked fine in 8.3.


Thank you all for ur replys.