RE: SQL: Using a host variable in an IN clause

Thanks Alan. I'll start with this approach and see what bubbles up next...

Eric

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Alan Campin
Sent: Tuesday, April 17, 2007 9:30 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: SQL: Using a host variable in an IN clause


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

I think you are on the right track to use a Global Temporary Table to
build the list.

Get the count of records in the temporary. If all divisions, it would
zero.

Exec SQL Set aCount = Select Count(*) from QTEMP/GlobalTemp;

Then the Where

Where aCount = 0 Or
DIVISION Exists (Select DIVISION From QTEMP/GlobalTemp)

The other way to do it would be if all divisions selected, just build
the list with all divisions. Either way would work.

Make sure you use a Global Temporary Table. A lot faster.