Forum Stats

  • 3,741,183 Users
  • 2,248,384 Discussions
  • 7,861,667 Comments

Discussions

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

426850
426850 Member Posts: 1,242
edited Sep 8, 2007 10:45AM in SQL & PL/SQL
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
    121256 Member Posts: 1,054
    RTFM [url http://oraclesvca2.oracle.com/docs/cd/B10501_01/server.920/a96540/functions19a.htm#999954]COALESCE
  • 426850
    426850 Member Posts: 1,242
    edited Sep 7, 2007 2:36AM
    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
    561825 Member Posts: 646
    edited Sep 7, 2007 2:46AM
    Why not a simple or condition ?

    Message was edited by:
    s.rajaram
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    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
    MichaelS Member Posts: 8,424 Bronze Crown
    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
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    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
    Vinay Reddy Member Posts: 186
    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
    426850 Member Posts: 1,242
    Thank you all for your interests, here the primary problem is performance and I wanted to see the alternatives.

    Best regards.
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    > 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.
  • Alessandro Rossi
    Alessandro Rossi Member Posts: 1,057 Bronze Badge
    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
    121256 Member Posts: 1,054
    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
    17777 Member Posts: 147
    edited Sep 7, 2007 4:14AM
    very nice solution... 5 stars
  • 584412
    584412 Member Posts: 1,329
    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
    MichaelS Member Posts: 8,424 Bronze Crown
    .. 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
    94799 Member Posts: 2,208
    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
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    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
    ascheffer Member Posts: 1,898 Gold Trophy
    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
    450441 Member Posts: 2,525
    edited Sep 7, 2007 5:12AM
    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
    94799 Member Posts: 2,208
    and the most often overlooked
    Yes I wonder why that is.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Sep 7, 2007 10:37AM
    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
    426850 Member Posts: 1,242
    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
    535698 Member Posts: 28
    LOL @ padders

    ;-)
This discussion has been closed.