where condition as variable (sql statement)

[Baron]

Is there a way to have a dynamic where condition in a sql statement (without the need to EXECUTE IMMEDIATE)?

begin
declare where_stmt varchar(100);
set where_stmt = ' salary > 100';
select * from employees where where_stmt;
end;

[VolkerBarth · Accepted solution]

You can vary particular values via connection-level variables. In V17, you can also vary table and column names via indirect identifiers. But to vary complete conditions including their operators (say, "salary > 100" vs. "salary in (100, 200, 300)" or the like), I guess you will have to use dynamic SQL.

For a small and fixed choice of conditions, you could of course use some kind of "condition type variable" that would allow to choose between several predefined condition branches, such as

   where (condition_type = 1 and salary > myVarValue)
      or (condition_type = 2 and salary < myVarValue)
      or (condition_type = 3 and SomeOtherColumn = myVarValue...)

probably with the need to cast your values to fitting types...

[Baron · Accepted solution]

Thank you very much for the reply.

The approach with condition_type is SUPER! It serves exactly the need of my case.

This is why I like to ask questions in this forum..

[Baron · Accepted solution]

I have one more question regarding indirect identifiers:

According to the documentation, should this block work, but on SQL Anywhere 17 it doesn't:

create or replace table dba.mytable (sn int, loc varchar(10));
CREATE OR REPLACE VARIABLE t_owner LONG VARCHAR = 'dba';
CREATE OR REPLACE VARIABLE t_name LONG VARCHAR = 'mytable';
SELECT * FROM '[t_owner]'.'[t_name]';

[VolkerBarth · Accepted solution]

See the comment by Jack Schueler with the corrected samples on the linked doc page (or the newer SAL Portal Help), you need back quotes for the references:

CREATE OR REPLACE VARIABLE t_owner LONG VARCHAR = 'GROUPO';
CREATE OR REPLACE VARIABLE t_name LONG VARCHAR = 'Employees';
SELECT * FROM `[t_owner]`.`[t_name]`;