I have a simple case statement as follows:
select
case WHEN upper(VALUE) is null then 'A_VALUE_ANYWAY' end test
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(VALUE)= 'NO_VALUE_IS_HERE'
This code is designed to return 'A_VALUE_ANYWAY' because there is no output from the SQL.
However, it does not return anything at all.
Essentially, what I would like is a value being forced to return from the case statement instead of just no rows.
Am I able to do that with the case statement? Is there some form of no data found handler I should be using instead?
I have examined this similar question but this is very complex and does not seem possible with this more simple statement
SQL CASE Statement for no data
Also, this, which uses a union to get a value from dual:
Select Case, when no data return
Which seems like a "Fudge" I feel like there must be some designed way to handle no data being found in a case statement.
CodePudding user response:
From Oracle 12, you can use the FETCH
syntax so that you do not have to query the table multiple times:
SELECT value
FROM (
SELECT value,
1 AS priority
FROM V$SYSTEM_PARAMETER
WHERE UPPER(VALUE)= 'NO_VALUE_IS_HERE'
UNION ALL
SELECT 'A_VALUE_ANYWAY',
2
FROM DUAL
ORDER BY priority
FETCH FIRST ROW WITH TIES
)
db<>fiddle here
CodePudding user response:
What you are asking is: "if my query returns no rows, I want to see a value". That cannot be solved with a case expression. A case expression transforms the results of your query. If there are no results, nothing can be transformed. Instead you could could modify your query and union it with another select from dual that returns a string if the query itself returns no results. That way either part of the UNION ALL
will return something.
SELECT
VALUE
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(VALUE)= 'NO_VALUE_IS_HERE'
UNION ALL
SELECT 'A_VALUE_ANYWAY'
FROM
DUAL
WHERE NOT EXISTS (SELECT 1
FROM
V$SYSTEM_PARAMETER
WHERE UPPER(VALUE)= 'NO_VALUE_IS_HERE'
This is the same technique as in the SQL Case statement for no data question.