RE: SQL - Using Case in Where clause

["Bob Cozzi" <cozzi@xxxxxxxxx>]

Try this instead
-Bob Cozzi

Select * from FILEA
    where CASE 
        when field1Filter = '>' then
             FIELD1 > :screenField1
        when field1Filter = '>=' then
            FIELD1 >= :screenField1
         .....
        end
-----Original Message-----
From: rpg400-l-bounces+cozzi=rpgiv.com@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+cozzi=rpgiv.com@xxxxxxxxxxxx] On Behalf Of Lim
Hock-Chai
Sent: Wednesday, June 16, 2004 3:38 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL - Using Case in Where clause

I'm getting the same error.  Seems like that is not a valid sql syntax.  I
guess my only choice is to use dynamic prepare statement.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of rob@xxxxxxxxx
Sent: Wednesday, June 16, 2004 2:57 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: SQL - Using Case in Where clause


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.


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


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