Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
usage of CASE in WHERE clause

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 182
Location: Coimbatore

PostPosted: Wed Mar 18, 2020 4:25 pm    Post subject: usage of CASE in WHERE clause
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: 236
Location: Bamberg, Germany

PostPosted: Wed Mar 18, 2020 5:25 pm    Post subject:
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: 182
Location: Coimbatore

PostPosted: Thu Mar 19, 2020 11:16 am    Post subject:
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: 2349
Location: Hampshire, UK

PostPosted: Thu Mar 19, 2020 2:57 pm    Post subject: Reply to: usage of CASE in WHERE clause
Reply with quote

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

Active User


Joined: 14 Sep 2009
Posts: 182
Location: Coimbatore

PostPosted: Thu Mar 19, 2020 5:10 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 2382
Location: NY,USA

PostPosted: Thu Mar 19, 2020 5:57 pm    Post subject:
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: 775
Location: Maryland

PostPosted: Fri Mar 20, 2020 5:08 pm    Post subject:
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: 182
Location: Coimbatore

PostPosted: Tue Mar 24, 2020 10:34 am    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 2382
Location: NY,USA

PostPosted: Tue Mar 24, 2020 10:44 am    Post subject:
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: 182
Location: Coimbatore

PostPosted: Tue Mar 24, 2020 3:28 pm    Post subject:
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: 1344
Location: Israel

PostPosted: Wed Mar 25, 2020 2:45 pm    Post subject: Re: usage of CASE in WHERE clause
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Usage of processor directive and firs... Sakthidevi PL/I & Assembler 4 Fri Apr 10, 2020 6:27 pm
No new posts Cobol redefines for Signed pictured c... gvvjags COBOL Programming 4 Thu Feb 27, 2020 4:33 pm
No new posts Usage of pre-processor statement in p... Sakthidevi PL/I & Assembler 1 Thu Nov 28, 2019 4:08 pm
No new posts Usage of external condition Sakthidevi PL/I & Assembler 1 Sat Nov 23, 2019 1:46 pm
No new posts Usage of Like statement in PL/I Sakthidevi PL/I & Assembler 3 Fri Nov 15, 2019 11:22 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us