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

Maybe try putting the 'in' inside the case clause and take the parentheses off the outside of the case clause.

fhsst
case
when fhtrm = 'COL' then in ('FL','WA' )
when fhtrm = 'PPD' then in ('GA','PA' )
end

or take the parentheses out of the middle - they are already specified around the case clause

fhsst in
(case
when fhtrm = 'COL' then 'FL','WA'
when fhtrm = 'PPD' then 'GA','PA'
end)

I don't know - maybe, huh?
HTH
Vern

At 12:05 AM 1/6/2005, you wrote:

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