Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

CASE Expression

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

New User


Joined: 10 Aug 2008
Posts: 45
Location: India

PostPosted: Wed Dec 22, 2010 4:40 am    Post subject: CASE Expression
Reply with quote

Hi,

I have a SQL as below.

SELECT
AWC.WORKFLOW_ACTN_ID
, WAL.LABEL_TXT
, WAL.LANGUAGE_CODE
, WCA.ACTN_GROUP_ID
FROM APPR_WRKFLW_CHAIN AWC
INNER JOIN WRKFLW_CHAIN_ACTN WCA ON
WCA.DOC_TYPE_CD = AWC.DOC_TYPE_CD
AND WCA.COUNTRY_CODE = AWC.COUNTRY_CODE
AND WCA.BUS_FORMAT_TYPE_CD = AWC.BUS_FORMAT_TYPE_CD
AND WCA.INPUT_TYPE_CODE = AWC.INPUT_TYPE_CODE
AND WCA.AD_GROUP_ID = AWC.AD_GROUP_ID
LEFT OUTER JOIN WRKFLW_ACTN_LABEL WAL ON
WAL.DOC_TYPE_CD = WCA.DOC_TYPE_CD
AND WAL.COUNTRY_CODE = WCA.COUNTRY_CODE
AND WAL.BUS_FORMAT_TYPE_CD = WCA.BUS_FORMAT_TYPE_CD
AND WAL.INPUT_TYPE_CODE = WCA.INPUT_TYPE_CODE
AND WAL.AD_GROUP_ID = WCA.AD_GROUP_ID
AND WAL.ACTN_SEQ_NBR = WCA.ACTN_SEQ_NBR
WHERE AWC.DOC_TYPE_CD = 5011
AND AWC.COUNTRY_CODE = 'US'
AND AWC.BUS_FORMAT_TYPE_CD = 496
AND AWC.INPUT_TYPE_CODE = 'D'
AND AWC.AD_GROUP_ID = 'FSSNAUS-APPOExpenseTeam'
AND WCA.WORKFLOW_ACTN_ID NOT IN
(CASE :WS-VAR
WHEN '0' THEN 4
WHEN '1' THEN
(3,4,13)
END)


This SQL gives -811 as CASE expression cannot have multiple values in THEN clause.

What is required:

I would like to select the data where WCA.WORKFLOW_ACTN_ID not in 3,4,13 when my WS-VAR is 0 and
WCA.WORKFLOW_ACTN_ID not in 3,4,13 when my WS-VAR is 1

Can someone help whether what would be the possible solution.

Thanks
K J
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Dec 22, 2010 1:36 pm    Post subject:
Reply with quote

why a case on :ws-var
create a table ws-tab with 3 elemenents

Code:
IF ws-var = 0
   move 4 to ws-tab-el (1)
   move 4 to ws-tab-el (2)
   move 4 to ws-tab-el (3)
ELSE
   move 3 to ws-tab-el (1)
   move 4 to ws-tab-el (2)
   move 13to ws-tab-el (3)
end-if

EXEC SQL
   ...
   and WCA.WORKFLOW_ACTN_ID NOT IN ws-tab
END-EXEC
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Dec 22, 2010 2:29 pm    Post subject:
Reply with quote

krithikaj wrote:
This SQL gives -811 as CASE expression cannot have multiple values in THEN clause


I do not think that is a correct (or rational) assumption.
a -811 has nothing to do with CASE expression.

would be interesting to know what is the data definition of WS-VAR

the CASE expression did not build a proper IN-list,
which would not cause a -811.

you got a -811 because your sql returned more than one row.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Dec 22, 2010 3:55 pm    Post subject:
Reply with quote

dbzTHEdinosauer wrote:
krithikaj wrote:
This SQL gives -811 as CASE expression cannot have multiple values in THEN clause


I do not think that is a correct (or rational) assumption.
a -811 has nothing to do with CASE expression.

would be interesting to know what is the data definition of WS-VAR

the CASE expression did not build a proper IN-list,
which would not cause a -811.

you got a -811 because your sql returned more than one row.

I don't think a CASE can return a IN-list, and you do get -811 if a case returns more than 1 value (result of a subselect).
I doubt if the SQL posted does precompile. probably gives an -104 on the , in the in-list.

you could solve it in SQL using
...
and case
when :ws-var = 0 and WCA.WORKFLOW_ACTN_ID <> 4 then 'OK'
when :ws-var = 1 and WCA.WORKFLOW_ACTN_ID not in (3,4,13) then 'OK'
else 'NOK' END = 'OK'
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Dec 22, 2010 5:29 pm    Post subject:
Reply with quote

I agrre Guy_C, if the CASE was generating a subselect, but
it was dealing with a Host-Variable.
Back to top
View user's profile Send private message
krithikaj

New User


Joined: 10 Aug 2008
Posts: 45
Location: India

PostPosted: Thu Dec 23, 2010 11:42 pm    Post subject: Reply to: CASE Expression
Reply with quote

Thanks for the replies. I am trying to use the CASE statement given by GUYC. I tried to run the SQL on QMF with one of the columns of the table and i didn't find any error.

I would notify the result if I get actual results correctly.

Regards
K J
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 Summing on Mixed Case Literals Roy Ware SYNCSORT 3 Tue Jan 03, 2017 10:18 pm
No new posts Case insenstive in INCLUDE Condtion smileseenu DFSORT/ICETOOL 2 Mon Feb 15, 2016 8:42 pm
No new posts How steps execute in Job in case of a... richiewalia JCL & VSAM 4 Tue Jan 12, 2016 12:34 pm
No new posts Translate UPPER CASE to lower case steve-myers PL/I & Assembler 0 Mon Oct 19, 2015 9:33 am
No new posts Clustered Index with UCASE Key Expres... Auryn DB2 2 Mon Feb 02, 2015 8:00 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us