CODEWITHSUNDEEP
oracle-databaseoracle-apex
Pablo
Pablo

Reputation: 29519

Handling -no records found- in Oracle query

The following Oracle query supposed to return rows of field num, when there are records. When no records are found I would like it to return N/A as display value and 0 as return value field.

select num display_value, num return_value from cells 
  WHERE ss = 4
UNION ALL SELECT "N/A" display_value, 0 return_value from dual 
  WHERE NOT EXISTS (SELECT 1 FROM CELLS WHERE ss = 4)

This doesn't work when no records found, because num is type of NUMBER and "N/A" is not NUMBER. Question is how can I amend query to work? Maybe there is better way to do it?

Upvotes: 0

Views: 559

Answers (1)

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

CAST or TO_CHAR?

select to_char(num) numdisplay_value, num return_value from cells 
  WHERE ss = 4
UNION ALL SELECT 'N/A' display_value, 0 return_value from dual 
  WHERE NOT EXISTS (SELECT 1 FROM CELLS WHERE ss = 4)

Maybe (look at your execution plan), you will get better results by using a CTE instead.

At the very least, this second solution will more readable (to my taste) and probably more maintainable according to the don't repeat yourself principle, as you will have to write the where clause only once:

with cte as (select to_char(num) numdisplay_value, num return_value 
             from cells 
             WHERE ss = 4)

select * from cte
union all SELECT 'N/A', 0 from dual 
      WHERE not exists(select * from cte)

Upvotes: 1

Related Questions