CODEWITHSUNDEEP
sqloracle-databaseselectplsqlwhere-clause
ChrisW
ChrisW

Reputation: 5068

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?

Upvotes: 2

Views: 57

Answers (3)

Mureinik
Mureinik

Reputation: 311438

You could emulate this behavior with the logical or operator:

SELECT var1, var2, var3
FROM   table1
WHERE  var1 IN (SELECT var1 FROM table2) AND
       (arg1 != 'some_val' OR var2 < 100)

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231671

It seems like you should be able to do this just with and and or conditions.

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

Of course, a procedure can't just do a naked select like this. You'd need to store the result somewhere, open a sys_refcursor, etc.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269933

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.

Upvotes: 5

Related Questions