SQL & PL/SQL

RTFM [url http://oraclesvca2.oracle.com/docs/cd/B10501_01/server.920/a96540/functions19a.htm#999954]COALESCE

Please show me with an example how coalesce will return the information I need which is; if any of the list members is NULL

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
Connected as hr

SQL> SELECT coalesce('x', 'y', '', 'z', NULL) "first-NON-null-expr-in-list"
  2    FROM dual
  3  /

first-NON-null-expr-in-list
---------------------------
x

Best regards

Why not a simple or condition ?

Message was edited by:
s.rajaram

I don't think coalesce is what you need, but maybe I'm missing something here.

You can use some nested nvl2 expression, but why doing it complicated. As s.rajaram says, using the or-operator will do just fine.

SQL> create table mytable
  2  as
  3  select 1 b, 1 c, 1 d, 1 e, 1 f, 1 g, 1 h from dual union all
  4  select 1, 1, 1, 1, null, 1, 1 from dual union all
  5  select null, null, null, null, null, null, null from dual union all
  6  select 1, null, 1, null, 1, null, 1 from dual
  7  /

Tabel is aangemaakt.

SQL> select b, c, d, e, f, g, h
  2       , coalesce(b,c,d,e,f,g,h)
  3       , nvl2(b, nvl2(c, nvl2(d, nvl2(e, nvl2(f, nvl2(g, h, null), null), null), null), null), null) anyone_null
  4    from mytable
  5  /

 B  C  D  E  F  G  H COALESCE(B,C,D,E,F,G,H) ANYONE_NULL
-- -- -- -- -- -- -- ----------------------- -----------
 1  1  1  1  1  1  1                       1           1
 1  1  1  1     1  1                       1

 1     1     1     1                       1

4 rijen zijn geselecteerd.

SQL> select b, c, d, e, f, g, h
  2    from mytable
  3   where nvl2(b, nvl2(c, nvl2(d, nvl2(e, nvl2(f, nvl2(g, h, null), null), null), null), null), null) is null
  4  /

 B  C  D  E  F  G  H
-- -- -- -- -- -- --
 1  1  1  1     1  1

 1     1     1     1

3 rijen zijn geselecteerd.

SQL> select b, c, d, e, f, g, h
  2    from mytable
  3   where b is null
  4      or c is null
  5      or d is null
  6      or e is null
  7      or f is null
  8      or g is null
  9      or h is null
 10  /

 B  C  D  E  F  G  H
-- -- -- -- -- -- --
 1  1  1  1     1  1

 1     1     1     1

3 rijen zijn geselecteerd.

Regards,
Rob.

Or this?:

select *
  from emp
 where exists (select null
                 from table (sys.dbms_debug_vc2coll (empno, comm, sal))
                where column_value is null)

Brilliant, michaels!

SQL> select b, c, d, e, f, g, h
  2    from mytable
  3   where exists
  4         ( select 'a null value'
  5             from table(sys.dbms_debug_vc2coll (b, c, d, e, f, g, h))
  6            where column_value is null
  7         )
  8  /

 B  C  D  E  F  G  H
-- -- -- -- -- -- --
 1  1  1  1     1  1

 1     1     1     1

3 rijen zijn geselecteerd.

Regards,
Rob.

You can try this!

SELECT *
FROM TABEL1
WHERE b IS NULL
OR c IS NULL
OR d IS NULL
OR e IS NULL
................ ..... ...

Thanx,

Cheers,

Thank you all for your interests, here the primary problem is performance and I wanted to see the alternatives.

Best regards.

> here the primary problem is performance and I wanted to see the alternatives.

Then the question will boil down to: is one full table scan a problem in your case? If not, the "several or" approach looks sufficient. If it is a problem, then you might need a function based index on the nvl2 expression.

Regards,
Rob.

nice one.

It has already been proposed but....

Where do you want to find something quicker than this

SELECT *
FROM T
WHERE a IS NULL
	OR b IS NULL
	OR c IS NULL
	...
	...
	OR z IS NULL

Bye Alessandro

very nice solution... 5 stars

JFF

SQL> create table nulltest (x int, y int, z int);

Table created.

SQL> insert into nulltest values (1,2,3);

1 row created.

SQL> insert into nulltest values (1,2,null);

1 row created.

SQL> insert into nulltest values (1, null,null);

1 row created.

SQL> insert into nulltest values (1, null,2);

1 row created.

SQL> select * from nulltest
  2  where x+y+z is null;

         X          Y          Z
---------- ---------- ----------
         1          2
         1
         1                     2

.. or still a bit shorter one when you are on 10g:

select * from emp 
 where lnnvl(sys.dbms_debug_vc2coll() not member of (sys.dbms_debug_vc2coll(empno, comm, sal)))

Based on michaels suggestion to treat the column list as a collection - an alternative to the subquery might be to use MULTISET operators to check for NULLs.

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> SELECT b, c, d, e, f, g, h
  2  FROM   mytable
  3  WHERE  sys.dbms_debug_vc2coll (NULL) MULTISET EXCEPT
  4            sys.dbms_debug_vc2coll (b, c, d, e, f, g, h) IS EMPTY;

         B          C          D          E          F          G          H
---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1                     1          1

         1                     1                     1                     1

SQL> SELECT b, c, d, e, f, g, h
  2  FROM   mytable
  3  WHERE  sys.dbms_debug_vc2coll (NULL) MULTISET INTERSECT
  4            sys.dbms_debug_vc2coll (b, c, d, e, f, g, h) IS NOT EMPTY;

         B          C          D          E          F          G          H
---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1                     1          1

         1                     1                     1                     1

SQL>

Very nice as well!

But no matter how brilliant, or how fun-to-write these solutions are, I cannot think of a valid reason in terms of performance and future maintenance, why not to write the "several or" variant.

Regards,
Rob.

select x.*
from (
select 1 b, 2 c, 1 d, 1 e, 1 f, 1 g, 1 h from dual
union all select 1, 1, 1, 1, null, 1, 1 from dual
union all select 1, 1, 1, 1, 1, 1, 1 from dual
union all select null, null, null, null, null, null, null from dual
union all select 1, null, 1, null, 1, null, 1 from dual
) x
where  case when ( b, c, d, e, f, g, h ) = all ( ( b, c, d, e, f, g, h ) ) 
         then 1
       end is null

Anton

Another option would surely be

WHERE NULL IN (b,c,d,e,f,g,h)

Seems the most straightforward to me (and the most often overlooked). Don't know about performance though.

EDIT: Forget this one - inlists with null members don't work. I'd taken it from my own code where I was checking for a particular value and the fields were all NOT NULL.

Message was edited by:
Dave Hemming

Yes I wonder why that is.

SQL> with WorkView as (select 1 b, 2 c, 1 d, 1 e, 1 f, 1 g, 1 h from dual
  2  union all select 1, 1, 1, 1, null, 1, 1 from dual
  3  union all select 1, 1, 1, 1, 1, 1, 1 from dual
  4  union all select null, null, null, null, null, null, null from dual
  5  union all select 1, null, 1, null, 1, null, 1 from dual)
  6  select b,c,d,e,f,g,h,
  7  case when greatest(b, c, d, e, f, g, h) is null then 1 else 0 end as "null1",
  8  case when Least(b, c, d, e, f, g, h) is null then 1 else 0 end as "null2"
  9  from WorkView;

   B     C     D     E     F     G     H  null1  null2
----  ----  ----  ----  ----  ----  ----  -----  -----
   1     2     1     1     1     1     1      0      0
   1     1     1     1  null     1     1      1      1
   1     1     1     1     1     1     1      0      0
null  null  null  null  null  null  null      1      1
   1  null     1  null     1  null     1      1      1

On greatest function and Least function,
if parameters have null,
these functions return null.

Thank you all for your interests and time,

JS1 : where x+y+z is null;

If the columns are not numeric you get "ORA-01722: invalid number" error

Rob van Wijk : Then the question will boil down to: is one full table scan a problem in your case? If not, the "several or" approach looks sufficient. If it is a problem, then you might need a function based index on the nvl2 expression.
Rob van Wijk : But no matter how brilliant, or how fun-to-write these solutions are, I cannot think of a valid reason in terms of performance and future maintenance, why not to write the "several or" variant.
Alessandro Rossi : Where do you want to find something quicker than this

I thought there could be an Analytics alternative versus Generic SQL for this need but upto now yes or method seems to be the best and since this is a daily report on an oltp(update, insert intensive) table function based indexing is not prefered.

best regards.

create table demo_table nologging as
    select * from dba_source union all
    select * from dba_source union all
    select * from dba_source
/

update demo_table set owner = NULL where mod(line, 43) = 0 ;
update demo_table set text = NULL  where mod(line, 11) = 0 ;
update demo_table set type = NULL where mod(line, 93) = 0 ;

analyze table demo_table compute statistics ;

select num_rows from user_tables where table_name = 'DEMO_TABLE' ;

  NUM_ROWS
----------
    462099

set timing on	
set autotrace traceonly statistics
select count(*) from demo_table
where nvl2(owner, nvl2(name, nvl2(type, nvl2(line, text, null), null), null), null) is null
/

-- each scenerio have the same consistent gets amount - 29903  
-- avg. of 5 runs is .37

select count(*) from demo_table
where owner is null
        or name is null
        or type is null
        or line is null
        or text is null
/
 
-- avg. of 5 runs is .17

select count(*) from demo_table
where exists
           ( select 'a null value'
               from table(sys.dbms_debug_vc2coll (owner, name, type, line, text))
              where column_value is null
           )
/

-- avg. of 5 runs is .87

select count(*) from (
select case when greatest(owner, name, type, line, text) is null then 1 else 0 end as x,
	   case when least(owner, name, type, line, text) is null then 1 else 0 end as y
from demo_table )
where x is null or y is null
/

-- avg. of 5 runs is .65

set autot off	 

LOL @ padders

;-)