CODEWITHSUNDEEP
sqloracle-database
Sarath
Sarath

Reputation: 39

Select columns if row exists else default values in oracle

In Oracle sql,I want to select few columns values from a table. If no row exists, i want to select default values for each columns. I need something like below

if exists (select 1 from mytable where key ='11') 
then
select key, value, comment from mytable where key ='11'
else
select 'key1' as "key", 'value1' as "value", 'default' as "comment"

What is the best way to do this in oracle.

Upvotes: 0

Views: 1045

Answers (2)

MT0
MT0

Reputation: 167981

WITH defaults ( key, value, comment ) AS (
  SELECT 'key1', 'value1', 'Default' FROM DUAL UNION ALL
  SELECT 'key2', 'value2', 'Default' FROM DUAL UNION ALL
  SELECT 'key3', 'value3', 'Default' FROM DUAL UNION ALL
  SELECT 'key4', 'value4', 'Default' FROM DUAL
)
SELECT COALESCE( t.key,     d.key     ) AS key,
       COALESCE( t.value,   d.value   ) AS value,
       COALESCE( t.comment, d.comment ) AS comment
FROM   defaults d
       FULL OUTER JOIN
       your_table t
       ON ( t.key = d.key );

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

Try this:

select
    key,value,comment
from mytable
where key = '11'
union all
select 'key1', 'value1', 'default' from dual
where not exists (select 1 from mytable where key = '11')

Upvotes: 2

Related Questions