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'

Toinette

Just now I realized I can just as easily do that: select a.* , case when (SELECT max(b.field1) FROM b where b.field2 = a.tbl_a_PK) is null then 'has no data in b' else 'has data in b' end as b_status from a. but I'm still curious as to what was the problem with the nvl. – Toinette

You need parentheses wrapping the select statement inside the function. It's better practice really to use Coalesce() rather than Nvl() – Lechner

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

Lounge

Answer 2

If you're searching for records in a which have/don't have associated records in b

select a.*, 
       case when b.field2 is null then 'has no data in b'
                                  else 'has data in b'
        as b_status
from a left outer join b
on a.tbl_a_PK = b.field2;

Should do it

Milliliter

Answer 3

the NVL(string1, replace_with) function requires 2 parameters, see docs here: http://www.techonthenet.com/oracle/functions/nvl.php
Ora 10g docs: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm
Since you know the problem, this query can fix it:

select a.*,
       case
         when (SELECT NVL(b.field2, 0) FROM b where b.field2 = a.tbl_a_PK and rownum = 1) > 0 then
          'has data in b'
         else
          'has no data in b'
       end b_status
  from a

and runs faster.
You don't need max() to check if the value exists in another table, simply check if the primary key is not null.

Whitfield