Select statement inside NVL

Question

I'm trying to run the following query:

select a.*, 
    case when NVL (SELECT max(b.field1)
        FROM b
        where b.field2 = a.tbl_a_PK , 'TRUE') = 'TRUE' 
            then 'has no data in b'
            else 'has data in b' end as b_status
from a

I checked and the select inside the nvl returns only 1 value (so there shouldn't be a problem there). However I'm getting 'ORA-00936: missing expression'

Answer 1

NVL() requires 2 parameters: expression to test and default value e.g. nvl(some_field, 111). You just need to isolate query parameter by braces and provide second parameter like in this statement:

select nvl( (select 1 from dual), 34) from dual 

In your variant parser expects comma after SELECT keyword and can't parse remaining string.

Exactly your statement must look like this:

select 
  a.*, 
  case when NVL(
              ( SELECT max(b.field1)
                FROM b
                where b.field2 = a.tbl_a_PK
              ), 
              'TRUE'
            ) = 'TRUE' 
       then 'has no data in b'
       else 'has data in b' end                  as b_status
from a

Hope this helps ...

Update In terms of performance is better to use exists rather then max :

select 
  a.*, 
  case when exists
              ( SELECT null
                FROM b
                where b.field2 = a.tbl_a_PK 
                      and 
                      b.field2 is not null
                      and 
                      rownum = 1
              ), 
       then 'has data in b'
       else 'has no data in b' end                  as b_status
from a