CODEWITHSUNDEEP
oracle-databaseplsql
Coding Duchess
Coding Duchess

Reputation: 6909

How to still return a default value when no record is found

When record is not found I want to return a default value, like N/A. I tried DECODE and NVL and NVL2 but still get nothing. My actual query is much more complex, just posting a simplified version

SELECT DECODE(test_date,NULL,'N/A', test_date) test_date
FROM  Table1
WHERE name = 'Unknown'

Upvotes: 0

Views: 1416

Answers (1)

Koen Lostrie
Koen Lostrie

Reputation: 18665

The default behaviour is that when no rows are found, nothing is shown. To work around that, you could the following: UNION the original select with another select that returns something if the original select has no rows. On the emp table that would be something like this (there is no user "xKING"):

SELECT ename FROM emp WHERE ename = 'xKING'
UNION 
SELECT 'N/A' FROM DUAL WHERE NOT EXISTS (SELECT ename FROM emp WHERE ename = 'xKING')
;

Functions like NVL and DECODE tranform the result columns - if there is no result they cannot be used.

Upvotes: 1

Related Questions