I have a query like this:
select data_name
into v_name
from data_table
where data_table.type = v_t_id
Normally, this query should return exactly one row. When there's no match on v_t_id
, the program fails with a "No data found" exception.
I know I could handle this in PL/SQL, but I was wondering if there's a way to do this only in a query. As a test, I've tried:
select case
when subq.data_name is null then
'UNKNOWN'
else
subq.data_name
end
from (select data_name
from data_table
where data_table.type = '53' /*53 does not exist, will result in 0 rows. Need fix this...*/
) subq;
...but this will obviously not work (because subq
being empty is not the same as subq.data_name is null
). Is this even possible or should I just check in my PL/SQL solution?
(oracle 10g)
There's ways to make this simpler and cleaner, but this basically spells out the technique:
SELECT data_name
FROM data_table
WHERE data_table.type = v_t_id
UNION ALL
SELECT NULL AS data_name
FROM dual
WHERE NOT EXISTS (
SELECT data_name
FROM data_table
WHERE data_table.type = v_t_id
)
When the first part of the union is empty the second will contain a row, when the first part is not empty, the second will contain no rows.
If the query is takes to much time, use this one:
SELECT * FROM (
SELECT data_name
FROM data_table
WHERE data_table.type = v_t_id
UNION ALL
SELECT NULL AS data_name
FROM dual
) WHERE data_name is not null or ROWNUM = 1