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

Vern, I appreciate your comments.  Currently I'm planning to handle up
to five values, so the list wouldn't be overly large.  I'd already
implemented a series of CASE statements that works fine but I worry
about performance (I'll have at least four IN's, not all of which will
be used on any one statement).

In thinking about this, though, dynamic SQL might be the better option
because I'll have a complex selection routine with multiple
multi-value IN's (interactive user input).  There will be cases where
none of the selection elements has a value, so I'd want to eliminate
that test completely, I can do this with a CASE in embedded SQL or
with RPG code in dynamic.

This leads to the question of examining the evaluated statement: if an
embedded CASE generates bloated code, I'd build what I need
dynamically.  I'll check the Redbook.

Thanks again,
Reeve


On Wed, 05 Jan 2005 08:00:14 -0600, Vernon Hamberg <vhamberg@xxxxxxxxxxx> wrote:
> 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.
> 
> --
> 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.
> 
>