Home » SQL & PL/SQL » SQL & PL/SQL » NVL not working in Cursor Subquery (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
NVL not working in Cursor Subquery [message #673958] Wed, 19 December 2018 13:24 Go to next message
vharish006
Messages: 67
Registered: August 2015
Location: Chicago
Member
Hi All,

There is no record for the below mentioned Client_Invoice_ID in PQINF.XXPQ_IP_INVOICE_CHARGES IPA table.So we want to replace 0 when no value exists.

Below query gives 0 as required but when using the same query in Cursor Subquery it is giving null value. what Can I do

SELECT NVL(IPA.TOTAL_AMOUNT,0) AMODISC
FROM PQINF.XXPQ_IP_INVOICE_CHARGES IPA,
PQINF.XXPQ_IP_INVOICE_HEADER HDR
WHERE IPA.CLIENT_INVOICE_ID IS NULL
AND HDR.CLIENT_INVOICE_ID='EBS-2349970'

Re: NVL not working in Cursor Subquery [message #673959 is a reply to message #673958] Wed, 19 December 2018 14:02 Go to previous messageGo to next message
BlackSwan
Messages: 26706
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: NVL not working in Cursor Subquery [message #673962 is a reply to message #673958] Wed, 19 December 2018 14:27 Go to previous messageGo to next message
Michel Cadot
Messages: 67137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No feedback in your previous topics.
No format.
Not posting what is required.
No help.

Re: NVL not working in Cursor Subquery [message #673971 is a reply to message #673962] Thu, 20 December 2018 05:08 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Null value != no rows
Re: NVL not working in Cursor Subquery [message #674009 is a reply to message #673971] Mon, 24 December 2018 11:26 Go to previous message
Bill B
Messages: 1955
Registered: December 2004
Senior Member
try the following

SELECT NVL(max(IPA.TOTAL_AMOUNT),0) AMODISC
FROM PQINF.XXPQ_IP_INVOICE_CHARGES IPA,
PQINF.XXPQ_IP_INVOICE_HEADER HDR
WHERE IPA.CLIENT_INVOICE_ID IS NULL
AND HDR.CLIENT_INVOICE_ID='EBS-2349970'

[Updated on: Mon, 24 December 2018 11:27]

Report message to a moderator

Previous Topic: Assign same Group id to rows
Next Topic: How to flatten one column from multiple rows
Goto Forum:
  


Current Time: Sat May 30 11:19:35 CDT 2020