Hi Tom,

I am struggling with a simple but tricky logic during SQL.

Requirement :- I want to call SQL from shell script passing character variable as input which could be null also

my sql :- select * from scott.emp where ename in (&1)
&1 possible values :- 1. 'JOHN'
                      2. 'JOHN','ROCKY'
                      3. Null ( NOthing to enter on user input) 

while input values 1 and 2 are working fine, when NULL is entered , it should not check any where condition and should display all records from emp table i.e. 1=1

I am facing problem with null handling in "IN" clause and bypassing the where clause.

Please help ....

create table t (
  c1 int
);

insert into t values ( 1 );

set define on
define v

old:select * from t
where  c1 in ( &v ) or &v is null
new:select * from t
where  c1 in ( null ) or null is null
C1   
    1 

select * from t
where  c1 in ( &v ) or &v is null;