indirect identifier for where clause

[Baron]

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?

[VolkerBarth · Accepted solution]

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

[Baron]

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;

[Baron]

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?

[VolkerBarth]

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

[Baron]

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

[Baron]

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

[VolkerBarth]

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.

[VolkerBarth]

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.