ChrisW ChrisW - 5 months ago 9
SQL Question

Optional where clause dependent on function argument

Given this pseudo code

create or replace procedure my_test
( arg1 IN varchar2 )
begin

select var1, var2, var3 from table1

where
var1 in (select var1 from table2)

if(arg1 = 'some_val')
and
var2 < 100
end if;
end;


Now, I know that
if
statements aren't permitted in
where
clauses, and all the examples of
case
statements I've seen only do variable assignment (rather than executing a clause) (e.g. here and here and here).

Is it possible to filter against a where clause conditionally dependent on the argument function at all using a
case
, or is the only way to use dynamic SQL?

Answer

You can convert you pseudo-code to SQL logic easily:

select var1, var2, var3
from table1
where var1 in (select var1 from table2) and
      ((arg1 = 'some_val' and var2 < 100) or
       (arg1 <> 'some_val')
      );

Note: you need to be careful with NULL values.

And, this can be simplified to:

where var1 in (select var1 from table2) and
      (var2 < 100 or arg1 <> 'some_val')

Also, it is a good idea to prefix variables with something so they are not confused with column names.