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.
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')
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 );
