Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to "where any of(b,c,d,e,f,g,h..) is null" with SQL(not PL/SQL)

426850Sep 7 2007 — edited Sep 8 2007
With the combinations of case, decode and coalesce function this can be done via pl/sql function, but is it possible to do it with SQL(any similar operator for this need I mean).

This need is because this is a report and the data processed is large so if this can be done within SQL engine it may help with performance.

Best regards.

Comments

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

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

561825
Why not a simple or condition ?

Message was edited by:
s.rajaram
Rob van Wijk

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.

MichaelS
Or this?:
select *
  from emp
 where exists (select null
                 from table (sys.dbms_debug_vc2coll (empno, comm, sal))
                where column_value is null)
Rob van Wijk

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.

Vinay Reddy
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,
426850
Thank you all for your interests, here the primary problem is performance and I wanted to see the alternatives.

Best regards.
Rob van Wijk
> 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.
561825
nice one.
Alessandro Rossi

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

121256
how coalesce will return the information I need which is; if any of the list members is NULL
Sorry. I'd confused with ALL.

I don't think that anything would be faster then ORs. But another example
decode(null, a,0, b,0, c,0, ...) = 0
for numneric:
a+b+c+... is null
17777
very nice solution... 5 stars
584412
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
MichaelS
.. 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)))
94799

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>
Rob van Wijk
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.
Anton Scheffer
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
450441
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
94799
and the most often overlooked
Yes I wonder why that is.
Aketi Jyuuzou
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.

426850

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	 
535698
LOL @ padders

;-)
1 - 23
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 6 2007
Added on Sep 7 2007
23 comments
5,383 views