You are inherrently attempting to equivalence check with a null value..

Check out the rules about nulls...
a = b is undefined if a or b is null.
your IN statement is effectively n=1 or n=2 or n=null - its this last one
that is 'undefined'...

you would need to change your code to...

n in (1,2) or n is null
or something similar...

hope this helps.
----- Original Message -----
From: "Rajavardhan "
<oracledba-ezmlmshield-x44293520.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Monday, April 30, 2007 12:26 PM
Subject: out put different


> Hi gurus
>
> I have table Test having only one column int type
>
>
>
> The data in the table is 1,2,3,null,4
>
>
>
> When run query using sql plus worksheet
>
> select * from test where n in(1,2,null);
>
>
>
> N
>
> ----------
>
> 1
>
> 2
>
>
>
> When run the same query in sql plus command prompt
>
>
>
> SQL> select * from test where n in(1,2,null);
>
>
>
> no rows selected
>
>
>
>
>
> plz give clear reason
>
>
>
>
>
> Thanks & Regards
>
>
>
> V.Rajavardhan Reddy
>
> Software Engineer
>
> GSS America Infotech Ltd.
>
> Block B, 3'rd Floor,
>
> Cyber Gateway,
>
> Hi-Tech City,
>
> Hyderabad.
>
> Tel:- 040-40028700
>
> Fax:- 040-40028703
>
> Visit us: http://www.gssamerica.com
>
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>


Oracle LazyDBA home page