Select default in case of no value returned

Question

I am trying to get some default value in my resultset if query does not return anything. I am trying nvl for the same but it is not returning the expected default value. To simulate, Consider following query,

select nvl(null, '10') from dual where 1=0;

I want to get 10 in case of given condition is not true and query does not return any value. However above query not returning any row.

Answer 1

Your query returns zero rows. NVL() isn't going to change that (*).

The correct solution is for the program which executes the query to handle NO_DATA_FOUND exception rather than fiddling the query.

However, you need a workaround so here is one using two sub-queries, one for your actual query, one to for the default.

When your_query returns an empty set you get this:

SQL> with your_qry as
  2      ( select col1 from t42 where 1=0 )
  3     , dflt as
  4      ( select 10 as col1 from dual  )
  5  select col1
  6  from your_qry
  7  union all
  8  select col1
  9  from dflt
 10  where not exists (select * from your_qry );

      COL1
----------
        10
SQL> 

And when it returns a row you get this:

SQL> with your_qry as
  2      ( select col1 from t42 )
  3     , dflt as
  4      ( select 10 as col1 from dual  )
  5  select col1
  6  from your_qry
  7  union all
  8  select col1
  9  from dflt
 10  where not exists (select * from your_qry );

      COL1
----------
        12
        13

SQL>

The WITH clause is optional here, it just makes it easier to write the query without duplication. This would have the same outcome:

select col1
from t42
where col0 is null
union all
select 10
from dual
where not exists (select col1 
                  from t42
                  where col0 is null)
;

---

(*) Okay, there are solutions which use NVL() or COALESCE() with aggregations to do this. They work with single column projections in a single row as this question poses, but break down when the real query has more than one row and/or more than one column. Aggregations change the results.

So this looks alright ...

SQL> with cte as (
  2      select 'Z' as col0, 12 as col1 from dual where 1=0 union all
  3     select 'X' as col0, 13 as col1 from dual where 1=0 )
  4  select
  5     nvl(max(col0), 'Y') as col0, nvl(max( col1), 10) as col1
  6  from cte;

COL0             COL1
---------- ----------
Y                  10

SQL> 

... but this not so much:

SQL> with cte as (
  2      select 'Z' as col0, 12 as col1 from dual union all
  3     select 'X' as col0, 13 as col1 from dual )
  4  select
  5     nvl(max(col0), 'Y') as col0, nvl(max( col1), 10) as col1
  6  from cte;

COL0             COL1
---------- ----------
Z                  13

SQL>

Answer 2

May be something like this is what you need

You could change WHERE clause (in this case WHERE COL > 1) similarly in both places.

WITH T(COL) AS(
    SELECT 1 FROM DUAL UNION ALL
    SELECT 2 FROM DUAL UNION ALL
    SELECT 3 FROM DUAL  
)
 SELECT COL FROM T WHERE COL > 1
 UNION ALL
 SELECT 10  AS COL FROM DUAL WHERE NOT EXISTS( SELECT 1 FROM T WHERE COL > 1)

Answer 3

You can use aggregation. An aggregation query always returns one row:

select coalesce(max(null), '10')
from dual
where 1 = 0;

I prefer coalesce() to nvl() because coalesce() is the ANSI standard function. But, nvl() would work here just as well.