Forum Stats

  • 3,752,640 Users
  • 2,250,531 Discussions
  • 7,867,903 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.
«13

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.
This discussion has been closed.