-1

We have a situation in oracle SQL.

select x from A where x=10;

so if 10 exists in the column, value will be displayed 10. but if it doesn't exists I want to display null. I tried NVL but it is not returning anything as data is not displaying.

Could you please help me to resolve this.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • Does this answer your question? [Return a value if no rows are found SQL](https://stackoverflow.com/questions/2679865/return-a-value-if-no-rows-are-found-sql) – PaulS Jan 30 '20 at 15:26
  • 1
    When there are more rows than one that match, do you want all that rows in the result or only ever one row? – sticky bit Jan 30 '20 at 15:47

3 Answers3

1

If you want to return multiple rows from the original SELECT then you can use UNION ALL to add a row when the value does not exist:

SELECT x
FROM   A
WHERE  x = 10
UNION ALL
SELECT NULL
FROM   DUAL
WHERE  NOT EXISTS (
  SELECT x
  FROM   A
  WHERE  x = 10
)

If your table is:

CREATE TABLE a ( x ) AS
SELECT 10 FROM DUAL UNION ALL
SELECT 10 FROM DUAL UNION ALL
SELECT 10 FROM DUAL;

Then the query will output:

|  X |
| -: |
| 10 |
| 10 |
| 10 |

Then if you DELETE FROM a; and repeat the query, it will output:

|    X |
| ---: |
| null |

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
0

Try this:

select max(x) 
from A
where x = 10; --this will return null if no data

And then you can do a NVL:

select nvl(max(x), 0) 
from A
where x = 10; --this will return 0 if no data
VBoka
  • 8,995
  • 3
  • 16
  • 24
0

Where clause filter out the data what you are looking for, if it doesn't exists then query will return no record.

So, you can remove where clause & do aggregation :

select max(case when a.x = 10 then 10 end)
from a;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52