IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

usage of CASE in WHERE clause


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore

PostPosted: Wed Mar 18, 2020 4:25 pm
Reply with quote

Hi,

I am facing an error while using CASE in WHERE condition. I am unable to fix it. Could you please help me to fix on how can I use the CASE condition.


Code:

SELECT  CUSTOMER_SUPP,               
        LOCATION,                     
        ITEMID,                       
        EFF_START,                   
        EFF_STOP,                     
        ORDER_METHOD,                 
        LOT_QUANTITY,                 
        KANBAN,                       
        RESPONSIBLE_USER,             
        EMPLOYEE,                     
        LIFE_CYCLE,                   
        SHIPPING_LOCATION,           
        VENDOR_SHARE_TYPE,           
        VENDOR_SHARE,                 
        CATEGORY,                     
        MINIMUM_LOT_COUNT,           
        CONTAINER_CODE               
FROM    TMMTEST.PRUKANB               
WHERE   BUSINESS_ENTITY = '03'       
AND     TYPE = 'CD'                   
AND     CSI_TYPE = 'SU'               
AND  (CASE BUSINESS_ENTITY           
       WHEN '03'                     
        THEN  (VENDOR_SHARE != 0       
                   AND VENDOR_SHARE_TYPE  =  'P')       
               WHEN '02'                               
                THEN  (((VENDOR_SHARE != 0             
                   AND   VENDOR_SHARE_TYPE  =  'P')     
                 OR     (VENDOR_SHARE_TYPE = 'R'       
                 OR      VENDOR_SHARE_TYPE = 'F')))     
               ELSE NULL                               
              END)                                     
        AND   ((EFF_START       <= '2020-03-18'         
          AND    ((EFF_STOP       >= '2020-03-18'       
          OR       CUSTOMER_SUPP   = '0091400')         
          OR      EFF_STOP       IS NULL))             
          OR    EFF_START         > '2020-03-18')       
        AND     ORDER_METHOD IN ('A',                   
                                 'C',                   
                                 'K',                   
                                 'B')                   
        ORDER BY CUSTOMER_SUPP,                         
                 LOCATION,                             
                 ITEMID,                               
                 EFF_START                             
  WITH UR;                                             

---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "!=". SOME SYMBOLS THAT MIGHT   
         BE LEGAL ARE: ??( [ CONCAT || / MICROSECONDS MICROSECOND SECONDS       
         SECOND                                                                 
DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                               
DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR                   
DSNT416I SQLERRD    = 3 0  0  -1  1685  502 SQL DIAGNOSTIC INFORMATION         
DSNT416I SQLERRD    = X'00000003'  X'00000000'  X'00000000'  X'FFFFFFFF'       
         X'00000695'  X'000001F6' SQL DIAGNOSTIC INFORMATION                   
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0                                       
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0                       
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72                 
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1                               
DSNE621I NUMBER OF INPUT RECORDS READ IS 46                                     
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 64                                 
******************************** Bottom of Data ********************************
Back to top
View user's profile Send private message
Joerg.Findeisen

Active User


Joined: 15 Aug 2015
Posts: 385
Location: Bamberg, Germany

PostPosted: Wed Mar 18, 2020 5:25 pm
Reply with quote

STFW results in telling that != is not valid SQL. Use <> there instead.
Back to top
View user's profile Send private message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore

PostPosted: Thu Mar 19, 2020 11:16 am
Reply with quote

I tried that already, but was unable to resolve the issue. It still told <> is invalid.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2448
Location: Hampshire, UK

PostPosted: Thu Mar 19, 2020 2:57 pm
Reply with quote

Try ¬=
Back to top
View user's profile Send private message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore

PostPosted: Thu Mar 19, 2020 5:10 pm
Reply with quote

It still throws the same error,


Code:
DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "^=". SOME SYMBOLS THAT MIGHT 
         BE LEGAL ARE: ??( [ CONCAT || / MICROSECONDS MICROSECOND SECONDS     
         SECOND                                                               
DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                               
DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR                   
DSNT416I SQLERRD    = 3 0  0  -1  1685  502 SQL DIAGNOSTIC INFORMATION         
DSNT416I SQLERRD    = X'00000003'  X'00000000'  X'00000000'  X'FFFFFFFF'       
         X'00000695'  X'000001F6' SQL DIAGNOSTIC INFORMATION                   
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2567
Location: NYC,USA

PostPosted: Thu Mar 19, 2020 5:57 pm
Reply with quote

In the THEN you must code a final value and not a condition and if you want a condition you should code another CASE.

That should solve these issues once fixed. Give a try.
Back to top
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 924
Location: Maryland

PostPosted: Fri Mar 20, 2020 5:08 pm
Reply with quote

The author tries to use COBOL-like syntax in SQL code.

This cannot work.

RTFM.
Back to top
View user's profile Send private message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore

PostPosted: Tue Mar 24, 2020 10:34 am
Reply with quote

Actually in THEN clause, it contains the query condition. Let me know how to use it ?. I cannot assign just one value and if i use another condition, I do not think it will come into that loop because when the business entity is '02' I need the entire below set,

WHEN '02'
THEN (((VENDOR_SHARE != 0
AND VENDOR_SHARE_TYPE = 'P')
OR (VENDOR_SHARE_TYPE = 'R'
OR VENDOR_SHARE_TYPE = 'F'))) .
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2567
Location: NYC,USA

PostPosted: Tue Mar 24, 2020 10:44 am
Reply with quote

Reread what I said, you need to use CASE if you want conditions , in THEN usually you should have a resulting value.

Rather tell us what’s your business case is and may then some can help in better way.
Back to top
View user's profile Send private message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore

PostPosted: Tue Mar 24, 2020 3:28 pm
Reply with quote

My requirement is,

1. Based on the business entity, I need the condition to be attached to the existing SELECT query in WHERE clause,

When BE is '03', I need a condition like below attached to the existing query,

Code:

(CASE BUSINESS_ENTITY           
       WHEN '03'                     
        THEN  (VENDOR_SHARE != 0       
                   AND VENDOR_SHARE_TYPE  =  'P')       



When BE is '02', I need a condition like below attached to the existing query,

Code:


      WHEN '02'                               
                THEN  (((VENDOR_SHARE != 0             
                   AND   VENDOR_SHARE_TYPE  =  'P')     
                 OR     (VENDOR_SHARE_TYPE = 'R'       
                 OR      VENDOR_SHARE_TYPE = 'F')))     
               
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1348
Location: Israel

PostPosted: Wed Mar 25, 2020 2:45 pm
Reply with quote

abdulrafi wrote:
Code:

SELECT  CUSTOMER_SUPP,               
...
        CONTAINER_CODE               
FROM    TMMTEST.PRUKANB               
WHERE   BUSINESS_ENTITY = '03'       
AND     TYPE = 'CD'                   
AND     CSI_TYPE = 'SU'         
     
AND  (CASE BUSINESS_ENTITY           
       WHEN '03'                     
        THEN  (VENDOR_SHARE != 0       
                   AND VENDOR_SHARE_TYPE  =  'P')       
               WHEN '02'                               
                THEN  (((VENDOR_SHARE != 0             
                   AND   VENDOR_SHARE_TYPE  =  'P')     
                 OR     (VENDOR_SHARE_TYPE = 'R'       
                 OR      VENDOR_SHARE_TYPE = 'F')))     
               ELSE NULL                               
              END)                                     
        AND   ((EFF_START       <= '2020-03-18'         
          AND    ((EFF_STOP       >= '2020-03-18'       
          OR       CUSTOMER_SUPP   = '0091400')         
          OR      EFF_STOP       IS NULL))             
          OR    EFF_START         > '2020-03-18')       
        AND     ORDER_METHOD IN ('A',                   
                                 'C',                   
                                 'K',                   
                                 'B')                   
 

Just no !
What happened to a plain
Code:
AND ((BUSINESS_ENTITY = '03' AND VENDOR_SHARE != 0       
                   AND VENDOR_SHARE_TYPE  =  'P')
OR (BUSINESS_ENTITY = '02' AND VENDOR_SHARE != 0       
                   AND VENDOR_SHARE_TYPE IN ('P', 'F', 'R')))
or something like that (not tested)

Anyway the whole original WHERE is just a big mess: the first "WHERE BUSINESS_ENTITY = '03'" already makes the "CASE"obsolete, the dates selection is a mess as well.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts DB2 group member usage DB2 0
No new posts NOT IN clause in COBOL pgm COBOL Programming 8
No new posts Any limit on usage of cursors ? DB2 1
No new posts SUSBSCRIPT WITH SIGN IN PIC CLAUSE COBOL Programming 3
No new posts Usage of processor directive and firs... PL/I & Assembler 4
Search our Forums:

Back to Top