SQL: Using a host variable in an IN clause

Hi all,

I am trying to encourage the use of static SQL in RPGIV in lieu of dynamic SQL
(OS400 V5R3). One of the issues I need to handle involves a varying list of
"divisions" that need to be processed in the statement. The list of divisions
(0..n) is built into an array, where blanks are considered unused elements. If
no elements, then omit the test.

Currently, the application loops through the array, looking for values, and
adding them to the IN (.............) clause. This string is then prepared and
executed, with results as expected.

For static SQL, I cannot see how to use a host variable with an IN clause....

I suppose one approach might involve creating a temporary table to hold the
selections, then use DIV = any (select * from templist) or DIV in (select *
from templist) in the WHERE criteria (or inner join). However, this won't work
if no divisions are in the list (implying that all divisions are valid). I can
see potential workarounds (using subselect IS NOT NULL) to short-circuit the
where clause for this piece, but all those subselects are ugly.....

Any tips on how variable length lists of values can be handled in static SQL?

thanks,
Eric