Re: SQL - Using Case in Where clause

[rob@xxxxxxxxx]

I tried something derived off of your sample and my compile dies on the 
greater than sign on the 
             FIELD1 > :screenField1
line.
SQL0104  30      58  Position 20 Token > was not valid. Valid tokens: END.
Sorry.  Here's my select anyway...
     C/EXEC SQL
     C+ Declare cur1 cursor for
     C+  Select * from filea
     C+  Where
     C+   case :Parm1
     C+    When '> ' then
     C+     Field1 > :testfield
     C+    When '>=' then
     C+     Field1>= :testfield
     C+   end
     C/END-EXEC


Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Lim Hock-Chai" <Lim.Hock-Chai@xxxxxxxx> 
Sent by: rpg400-l-bounces+rob=dekko.com@xxxxxxxxxxxx
06/16/2004 01:22 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc

Fax to

Subject
SQL - Using Case in Where clause






If FILEA contains FIELD1 and FIELD2 and I want to give user an option to 
selection records by FIELD1 that is >, >=, =, <, or <= the value he/she 
entered on the screen, what would be the best way to do this using Case in 
where clause?  Let say host variable field1Filter contains the option that 
user selected.
 
I wish I can do below, but it is not a valid SQL statement.
 
Select * from FILEA
    where case field1Filter 
        when '>' then
             FIELD1 > :screenField1
        when '>=' then
            FIELD1 >= :screenField1
         .....
        endcase
 
Any suggestion?
 
 
Note: Do not wish to use dynamic prepare sql statement.
 
thanks
 
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.