Re: SQL: using a host variable with the "IN" keyword -- MIDRANGE-L

You need to use a list of host variables. At least, that is what the doc suggests. You would need as many as you ever need, say 50 or 52 in this case. So something like

in (:state01, :state02, ... ,:state50)

You'd use as many as you need and put a dummy value, say x'FFFF' in the remaining host variables. that value almost guarantees no match. There is a performance hit for items not found, as the entire list will need to be searched.

AFAIK, the only way to get a variable list is to use dynamic SQL, not embedded. And host variables cannot be used in dynamic SQL, IIRC.

Ops Nav provides the ability to watch SQL. Or use the database monitor - STRDBMON gets it going. The results go into a PF you specify. The record structures are ugly - each record has a type, and there are join fields between records. There is a Redpaper or whatever at http://www.redbooks.ibm.com/redpapers/pdfs/redp0502.pdf

HTH
Vern

At 02:10 AM 1/5/2005, you wrote:

I'm trying to format a host variable to support the IN keyword (I'm
using hard-coded IN statements without problem).  The hard-coded
expression would be "...where type in('FL','WA','PA')"; I want to
build the IN keyword predicate dynamically for eventual use in a CASE
statement.

My prototype program has this code:
"Declare Input cursor for select order, state from Orders where state
in :state_list"

It compiles but ti doesn't work

I can built a series of CASE statements to get around this, but I have
one of those feelings (again) that I've overlooked something.

I'd greatly appreciate a working example of using a host variable with
the IN keyword.

Also, is there any way I can view the evaluated SQL statement?  I'm
trying various combinations of things but I'm not getting any SQL
errors; it would be handy to see what SQL is trying to do.

Thanks,
Reeve
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.