RE: SQL - Using Case in Where clause

CASE is designed to return a value not conditionally execute part of a 
statement.  You might get it to return part of an expression but my attempts to 
do it weren't successful.

In part I think this may be because the analyzer engine uses the where clause 
to determine the access plan for the query.  Using CASE in the WHERE clause 
doesn't give the analyzer anything to work with.

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Lim Hock-Chai
Sent: Monday, June 21, 2004 2:41 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL - Using Case in Where clause


Still getting the same error.

     C/exec sql
     C+ declare C2 cursor for
     C+   select CLBLNO, CLCUNO, CLSFNO, CLCY, CLYR,
     C+          CLMO, CLDY, CLHR, CLMN, CLSC
     C+   from CSLOGPF
     C+   where
     C+      case
     C+         when filterType = 'FORM' then
     C+            CLFRMC = :SCFRMC
SQL0104: Position 27 Token = was not valid. Valid tokens: END.
     C+         else
     C+            null
     C+      end
     C+   order by
     C+      case :sortType
     C+         when 'STD' then
     C+            CLBLNO, CLCUNO, CLSFNO, CLCY DESC, CLYR DESC,
     C+            CLMO DESC, CLDY DESC
     C+         else
     C+            null
     C+      end
     C/end-exec         

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Bob Cozzi
Sent: Monday, June 21, 2004 1:47 PM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: SQL - Using Case in Where clause


Why are you using ENDCASE?  It should just be "END" not "ENDCASE" I thought
I pointed that out already.
-Bob

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