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;

asked 30 Sep '22, 05:28

Baron's gravatar image

Baron
2.1k134146174
accept rate: 46%

edited 30 Sep '22, 05:29


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

permanent link

answered 30 Sep '22, 06:31

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

2

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

(30 Sep '22, 06:45) Baron
Replies hidden

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

(04 Oct '22, 10:21) Baron
1

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]`;
(04 Oct '22, 11:06) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×69
×10
×7

question asked: 30 Sep '22, 05:28

question was seen: 404 times

last updated: 04 Oct '22, 11:07