Question Re: where condition as variable (sql statement) in Technology Q&A

where condition as variable (sql statement)

Baron — Fri, 30 Sep 2022 05:28:52 GMT

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;

Re: where condition as variable (sql statement)

VolkerBarth — Fri, 30 Sep 2022 06:31:47 GMT

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...

Re: where condition as variable (sql statement)

Baron — Fri, 30 Sep 2022 06:45:07 GMT

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..

Re: where condition as variable (sql statement)

Baron — Tue, 04 Oct 2022 10:21:56 GMT

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]';

Re: where condition as variable (sql statement)

VolkerBarth — Tue, 04 Oct 2022 11:06:17 GMT

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]`;