Forum Stats

  • 3,750,091 Users
  • 2,250,105 Discussions
  • 7,866,776 Comments

Discussions

Categories

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.
· Share on TwitterShare on Facebook
«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
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 561825
    561825 Member Posts: 646
    edited Sep 7, 2007 2:46AM
    Why not a simple or condition ?

    Message was edited by:
    s.rajaram
    · Share on TwitterShare on Facebook
  • 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.
    · Share on TwitterShare on Facebook
  • 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)
    · Share on TwitterShare on Facebook
  • 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.
    · Share on TwitterShare on Facebook
  • 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,
    · Share on TwitterShare on Facebook
  • 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.
    · Share on TwitterShare on Facebook
  • 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.
    · Share on TwitterShare on Facebook
  • 561825
    561825 Member Posts: 646
    nice one.
    · Share on TwitterShare on Facebook
This discussion has been closed.