|
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? |
|
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. :) |
|
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; 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
|