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

426850 · Sep 7, 2007 1:54AM

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.

121256 · Sep 7, 2007 2:10AM

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

426850 · Sep 7, 2007 2:34AM

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 · Sep 7, 2007 2:44AM

Why not a simple or condition ?

Message was edited by:
s.rajaram

Rob van Wijk · Sep 7, 2007 2:52AM

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 · Sep 7, 2007 2:53AM

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 · Sep 7, 2007 2:57AM

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 · Sep 7, 2007 3:02AM

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 · Sep 7, 2007 3:04AM

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

Best regards.

Rob van Wijk · Sep 7, 2007 3:09AM

> 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 · Sep 7, 2007 3:09AM

nice one.

Alessandro Rossi · Sep 7, 2007 3:43AM

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 · Sep 7, 2007 4:13AM

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 · Sep 7, 2007 4:14AM

very nice solution... 5 stars

584412 · Sep 7, 2007 4:15AM

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 · Sep 7, 2007 4:29AM

.. 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 · Sep 7, 2007 4:36AM

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 · Sep 7, 2007 4:49AM

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.

ascheffer · Sep 7, 2007 5:00AM

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 · Sep 7, 2007 5:10AM

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 · Sep 7, 2007 5:11AM

and the most often overlooked

Yes I wonder why that is.

Aketi Jyuuzou · Sep 7, 2007 6:39AM

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 · Sep 8, 2007 4:02AM

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 · Sep 8, 2007 10:45AM

LOL @ padders

;-)

Forum Stats

Howdy, Stranger!

Quick Links

Categories

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

Comments

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Howdy, Stranger!

Categories

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

Comments