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

[Reeve <rfritchman@xxxxxxxxx>]

Here's a routine to test CASE and IN:

SELECT fhpro,fhtrm,fhsst FROM frp001 
WHERE                                
fhsst in                             
(case                                
when fhtrm = 'COL' then ('FL'     )  
when fhtrm = 'PPD' then ('GA'     )  
end)                                 
ORDER BY fhtrm                       

But this doesn't work, the difference being a list of values for IN:

SELECT fhpro,fhtrm,fhsst FROM frp001 
WHERE                                
fhsst in                             
(case                                
when fhtrm = 'COL' then ('FL','WA' )  
when fhtrm = 'PPD' then ('GA','PA' )  
end)                                 
ORDER BY fhtrm                       

The error identified is the comma separating 'FL' and 'WA'.  I'm
trying to build a CASE statement because I want to exclude the
condition (the IN predicate) if no values are entered (i.e. if I don't
have any selection criteria, I want to select everything).  I can
spoof the select for "all" with

fhsst > ' '

The fact that a single IN value works and a list doesn't makes me
think this is working like passing lists to commands in CL works.

-reeve

On Wed, 5 Jan 2005 07:49:56 -0800, Reeve <rfritchman@xxxxxxxxx> wrote:
> 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.mi