Oracle case statement not returning values for no row results-CodePudding

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.