Hello Tom
Is it possible to change the where condition (using case statement) based on certain variable?
For example
var T varchar2(1)
exec :T := 'E';
var E number;
exec :E := 7788;
var N varchar2(20)
exec :N := 'MILLER';
select empno, ename
from emp
where
-- how to use case statement to vary the condition based on :T
-- if :T = 'E' then the condition should be
where empno = :E
-- and if :T = 'N' then the condition should be
where ename like :N
Wellll, you can kinda do this by having a case expression on either side of your condition.
For example:
var T varchar2(1)
exec :T := 'E';
var E number;
exec :E := 100;
var N varchar2(20)
exec :N := 'Neena';
select employee_id, first_name
from hr.employees
where case
when :T = 'E' then to_char ( :E )
when :T = 'N' then :N
end = case
when :T = 'E' then to_char ( employee_id )
when :T = 'N' then first_name
end;
EMPLOYEE_ID FIRST_NAME
100 Steven
exec :T := 'N';
select employee_id, first_name
from hr.employees
where case
when :T = 'E' then to_char ( :E )
when :T = 'N' then :N
end = case
when :T = 'E' then to_char ( employee_id )
when :T = 'N' then first_name
end;
EMPLOYEE_ID FIRST_NAME
101 Neena But this is a
TERRIBLE idea!
Using expressions like this destroys the optimizer's ability to use indexes. So these queries will be slow.
It's far better to inspect the value of T first, then run the appropriate (static) SQL statement:
begin
if :T = 'E' then
select ...
into ...
from hr.employees
where employee_id = :E;
elsif :T = 'N' then
select ...
into ...
from hr.employees
where first_name = :N;
end if;
end;
/