Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Posts
    45

    Unanswered: Problem with "Not in " operator

    I have a table with 2 columns
    The cloumn cp_lot_status can be null or 'PL','RE'
    The following shows the current data

    SQL> SELECT CP_LOT_NUMBER,CP_LOT_STATUS FROM CP_LOTS_GENERATED ;

    CP_LOT_NUMBER CP
    --------------- --
    203750001
    103750001


    SQL> SELECT * FROM CP_LOTS_GENERATED WHERE CP_LOT_STATUS NOT IN ('PL','RE') AND
    2 CP_LOT_STATUS IS NULL;


    no rows selected

    SQL> DESC CP_LOTS_GENERATED
    Name Null? Type
    ----------------------------------------------------- -------- --------------------
    CP_LOT_NUMBER NOT NULL NUMBER(12)
    CP_LOT_STATUS VARCHAR2(2)


    So my question is does "Not in " operator not show the null values while doing the above query.IF so what could i do to show the null value rows

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Problem with "Not in " operator

    NULLs are tricky. They are never equal to, not equal to, IN or NOT IN anything. So this predicate:

    WHERE CP_LOT_STATUS NOT IN ('PL','RE')

    is neither true nor false when cp_lot_status is NULL - it evaluates to NULL. The query only returns rows where all predicates evaluate to TRUE.

    As for what you could do - well, just remove that predicate, it was redundant anyway:

    SELECT * FROM CP_LOTS_GENERATED
    WHERE CP_LOT_STATUS IS NULL;

  3. #3
    Join Date
    May 2003
    Posts
    45

    Re: Problem with "Not in " operator

    But the values 'PL' and 'RE' may occur at some point of time......

    Originally posted by andrewst
    NULLs are tricky. They are never equal to, not equal to, IN or NOT IN anything. So this predicate:

    WHERE CP_LOT_STATUS NOT IN ('PL','RE')

    is neither true nor false when cp_lot_status is NULL - it evaluates to NULL. The query only returns rows where all predicates evaluate to TRUE.

    As for what you could do - well, just remove that predicate, it was redundant anyway:

    SELECT * FROM CP_LOTS_GENERATED
    WHERE CP_LOT_STATUS IS NULL;

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Problem with "Not in " operator

    Well, if they did you would never see them with a query that contained the predicate "AND CP_LOT_STATUS IS NULL".

    Maybe what you want is

    WHERE (CP_LOT_STATUS NOT IN ('PL','RE')
    OR CP_LOT_STATUS IS NULL);

  5. #5
    Join Date
    May 2003
    Posts
    45

    Re: Problem with "Not in " operator

    yeah this worked out

    thanx......................

    Originally posted by andrewst
    Well, if they did you would never see them with a query that contained the predicate "AND CP_LOT_STATUS IS NULL".

    Maybe what you want is

    WHERE (CP_LOT_STATUS NOT IN ('PL','RE')
    OR CP_LOT_STATUS IS NULL);

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •