NVL and no rows returned
chris227Jan 11 2010 — edited Jan 11 2010Hi,
i found a little error in coded in an SQL-Construct equivalent to this one:
SELECT NVL(dummy, 'A') from dual where 1=2
This will return nothing instead of the expected 'A', because in my understanding no rows are returned and though no NULL could be replaced.
BUT, at least surprising for me,
SELECT NVL(
(SELECT NVL(dummy, 'A') from dual where 1=2), 'B')
from dual
will return 'B'.
What would be a good explanation of this behaviour?
I found nothing in the documentation on this.
Strange enough for me, in the net often other 'workarounds" are proposed, like
SELECT * from(
SELECT NVL(dummy, 'A') from dual where 1=2
UNION ALL
SELECT 'A' from dual)
where
rownum = 1
or with the use of EXISTS.
Are there any drawbacks of the simple NVL-wrapper?
Regards
Chris
Edited by: chris227 on 11.01.2010 01:43