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

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;

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

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);

yeah this worked out

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