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 ....
To return rows when the input is empty, you also need to check if it IS NULL:
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;
If you're coding this in an application (not a SQL*Plus/SQLcl script), you should use bind variables. Doing a variable IN list with these is trickier; for solutions see:
https://asktom.oracle.com/pls/apex/asktom.search?tag=how-can-i-do-a-variable-in-list-200005 You'll still need the IS NULL check when using binds.