1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to skip the row if it contains null value,.

Discussion in 'SQL PL/SQL' started by Vicky, Jul 7, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi frndz.,

    I have 2 questns with the following qury.,

    select * from (select employees.*, rank() over (order by salary desc) as rk from employees) where rk<5;

    which displays O/P as:

    SNO EMP_ID EMP_NAME SALARY DOB RK
    --- ------ -------------------------------------------------- ------
    (null) (null) (null) (null) (null) 1
    2 20 bala 66000 01-JAN-91 2
    3 30 celin 65988 02-FEB-99 3
    8 8 McCullam 50000 21-MAR-96 4

    i) Why it's giving 1'st rank to record having NULL values?!
    ii) I want to skip the record if any of the column has NULL value.,.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    For skip rows (an exception of rows of selection) it is necessary to use filtering...

    Simple example.

    1)
    Code (SQL):

    rem v1
    WITH example AS
    (
        SELECT
             level id ,
             CASE WHEN MOD(level,2) = 0 THEN chr(64+level) END str,
            NULLIF(MOD(level,3),0) val
        FROM  dual
        CONNECT BY level <= 7
    )
    SELECT
        e.*
        ,rank()  OVER (ORDER BY str ) rnk
    FROM example e;

    rem v2
    WITH example AS
    (
        SELECT
             level id ,
             CASE WHEN MOD(level,2) = 0 THEN chr(64+level) END str,
            NULLIF(MOD(level,3),0) val
        FROM  dual
        CONNECT BY level <= 7
    )
    SELECT
        e.*
        ,rank()  OVER (ORDER BY str ) rnk
    FROM example e
    WHERE  1 = nvl2(str,nvl2(val,1,0),0);

    SQL>
    SQL>
     
            ID STR         VAL        RNK
    ---------- ---- ---------- ----------
             2 B             2          1
             4 D             1          2
             6 F                        3
             3                          4
             7               1          4
             5               2          4
             1               1          4
     
    7 ROWS selected
     
            ID STR         VAL        RNK
    ---------- ---- ---------- ----------
             2 B             2          1
             4 D             1          2

     
    2) or to used clause in order by .. nulls first |last
     
    Vicky likes this.
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,

    Just add "NULLS LAST" to the ORDER BY:

    Code (SQL):
    SELECT * FROM (SELECT employees.*, rank() OVER (ORDER BY sal DESC NULLS LAST) AS rk FROM employees) WHERE rk<5
    ;

    It won't "skip" the row but it will make it appear last ... and with rk < 5 you won't see it :)
     
    Vicky likes this.
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    What no one explicitly stated in answer to question one is that by default a NULL value is treated as being infinitely high. The NULLS LAST and NULLS FIRST keywords can be used to change this behavior:

    Code (Text):
    create table temp_sort (col1 NUMBER, col2 NUMBER);
    INSERT INTO temp_sort values (1, 0);
    INSERT INTO temp_sort values (2, 100);
    INSERT INTO temp_sort values (3, 10000);
    INSERT INTO temp_sort values (4, 10000000);
    INSERT INTO temp_sort values (5, NULL);

    SELECT * FROM temp_sort ORDER BY col2;
         COL1       COL2
    ---------- ----------
             1          0
             2        100
             3      10000
             4   10000000
             5          

    SELECT * FROM temp_sort ORDER BY col2 DESC;
          COL1       COL2
    ---------- ----------
             5            
             4   10000000
             3      10000
             2        100
             1          0

    SELECT * FROM temp_sort ORDER BY col2 DESC NULLS LAST;
          COL1       COL2
    ---------- ----------
             4   10000000
             3      10000
             2        100
             1          0
             5            

    SELECT * FROM temp_sort ORDER BY col2 NULLS FIRST;
          COL1       COL2
    ---------- ----------
             5            
             1          0
             2        100
             3      10000
             4   10000000
     
    Vicky likes this.
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi Sergey,

    The Query works fine by skipping the records having null values.,.
    But, Could U tel me how the where condition works here, bcz U've given like 1=.,

    WHERE 1 = nvl2(str,nvl2(val,1,0),0);
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.
     
  7. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,
    To complement above, the syntax for the Oracle/PLSQL NVL2 function is:

    And to put it simply, what Sergey is trying to do elegantly is: to exclude records with either "str" and "val" having NULL values.

    If you want, we can replace:

    by

    Code (SQL):
    WHERE str IS NOT NULL AND val IS NOT NULL;
     
    Vicky likes this.
  8. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Yes Jagadekara. I could understand the NVL2 functionality. But I can't get how he's giving where 1= .....

    I wanna know how Where 1=.... works.,.,

    Is it possible to use a character like '1' instead of column_name., to make it work.,.
     
  9. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Yes Vicky, you can put '1' or column_name on the left or right of operator ("=" here), i.e.,

    Code (SQL):
    1 = nvl2(str,nvl2(val,1,0),0);
     
    is equivalent to

    Code (SQL):
    nvl2(str,nvl2(val,1,0),0) = 1;
     
    If val is null, then nvl2(val,1,0) returns 0, if str is null, then nvl2(str,0,0) returns 0 => 0=1 => FALSE therefore this row will not be selected.

    You can deduce for the other combinations.
     
    Vicky likes this.
  10. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Tnx for the clear explanation Rajen.,,.