CODEWITHSUNDEEP
oracle-databaseoracle11g
sks
sks

Reputation: 157

NVL function not returning expression 2

in following query, accoount_num is varchar2(20), not returning NA. 999910026A is not there in my table. I am using oracle database. It looks very strange to me.

select NVL(account_num,'NA') 
from account 
where account_num='999910026A'

Please help me in understanding this. enter image description here

Thanks in advance

Upvotes: 1

Views: 168

Answers (2)

trincot
trincot

Reputation: 350760

Assuming the account_num field has unique values in the table (maybe even the key?), you can do it by applying an aggregation -- that will always return a result: one result.

select nvl(min(account_num),'NA') 
from account 
where account_num='999910026A'

Upvotes: 1

Mureinik
Mureinik

Reputation: 311853

There are no rows in the table with account_num='999910026A', so no rows are returned, and there's nothing to apply nvl on. One way to emulate this behavior is with a left join:

SELECT    NVL(account_num, 'NA') 
FROM      (SELECT '999910026A' AS literal
           FROM   dual) l
LEFT JOIN account ON account_num = literal

Upvotes: 0

Related Questions