Is it possible to have a dynamic where statement, something like this.

begin
declare wherestmt long varchar;
set wherestmt  = '(''1'', ''2'')';
select * from MYTABLE where id in wherestmt;
end;

What are other alternatives rather than execute immediate?

asked 04 Apr '23, 04:32

Baron's gravatar image

Baron
2.1k134146174
accept rate: 46%


If you want to have variable IN lists, see this FAQ:

IN search condition - expression-list represented by a variable

Aside: In my understanding "indirect identifiers" deal with parametrizing names of tables or columns (like "MYTABLE" and "id" in your sample), not with parametrizing values. :)

permanent link

answered 04 Apr '23, 06:36

Volker%20Barth's gravatar image

Volker Barth
40.0k361549819
accept rate: 34%

edited 04 Apr '23, 06:49

Thanks for the hint, so it worked also simply like this:

begin
declare wherestmt long varchar;
set wherestmt  = '1,2';
select * from MYTABLE where id in (select row_value from sa_split_list(wherestmt));
end;
permanent link

answered 04 Apr '23, 15:35

Baron's gravatar image

Baron
2.1k134146174
accept rate: 46%

This is also works regardless of datatype of column id (whether int or varchar).

Of course is better to omit the unwanted whitespaces like this:

begin
declare wherestmt long varchar;
set wherestmt  = '1,   2';
select * from MYTABLE where id in (select replace(row_value, ' ', '') from sa_split_list(wherestmt));
end;

@Volker Barth, what are the drawbacks with this approach?

(05 Apr '23, 02:55) Baron
Replies hidden

If your lists are generally delimited by ', ' (i.e. including a space after the comma), you can easily use that as delimiter aka the 2nd parameter for sa_split_list without the need to use replace, such as

...from sa_split_list(wherestmt, ', ')...

And if your lists may or may not use spaces after the comma, I would prefer to use trim(row_value) to omit only leading or trailing spaces - the replace call would also delete intermitting spaces - something you won't want if you had a list of real character values that might contain spaces themselves, such as

...wherestmt = 'white, black, sky blue, dark red';...
(05 Apr '23, 03:16) Volker Barth

Yes I understand this with whitespaces, but I wanted to know what is your point for this simple solution in general, in comparison with the other solution/s listed here

(05 Apr '23, 03:43) Baron

yes, ltrim and rtrim are much more better than just replacing whitespaces with nothing (to avoid unintentionally removing of middle whitespaces).

(05 Apr '23, 03:47) Baron
1

I don't see a drawback, it's a good solution IMVHO. The approaches from the other FAQ seem more partly complicated, but that's partly due to the fact that they contain the generation of test data, too...

I can't comment on performance but I don't see problems there.

(05 Apr '23, 03:56) Volker Barth

I mostly prefer trim() over ltrim() and rtrim() unless I really want to treat only leading or only trailing spaces.

Aside: In current versions, trim(), ltrim() and rtrim() can also remove other characters, see here.

(05 Apr '23, 04:02) Volker Barth
showing 1 of 6 show all flat view
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:

×10
×7

question asked: 04 Apr '23, 04:32

question was seen: 451 times

last updated: 05 Apr '23, 04:02