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 statement within WHERE clause

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

New User


Joined: 13 Jul 2007
Posts: 18
Location: Pennsylvania

PostPosted: Wed Mar 17, 2010 3:25 pm    Post subject: CASE statement within WHERE clause
Reply with quote

Hi,

I need help to use CASE Statement within my WHERE Clause. I want to change the WHERE Clause based on my condition as following:

SELECT ...

FROM ...

WHERE (condition)

AND (condition)

AND (condition)

AND (
CASE WHEN IN_TYPE_FLAG = 'R'
THEN (REGNR_NA = 0) --- Problem
WHEN IN_TYPE_FLAG = 'S'
THEN (REGNR_NA <> 0) --- Problem
-- ELSE -- when in type flag = 'a'
END

)

In the above case, my WHERE Clause is dependent on one of the fields in a table and an input field. If the input field is 'R' then only records where the REGNR_NA is zeroes must be selected; if the input field is 'S' only records with REGNR_NA not equal zeroes must be selected otherwise if the input flag is 'A' then any records can be selected where REGNR_NA is zeroes or not.

I am getting error on the mentioned line (Problem Line). Since CASE is an expression and does not execute a statement, can anyone help me to get my WHERE Clause working... It is specify in my specs that I must use a CASE statement.

Thanks in advance...
icon_confused.gif [/b]
Back to top
View user's profile Send private message

Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Wed Mar 17, 2010 4:27 pm    Post subject:
Reply with quote

Hi,
You may try this.
Code:
AND
((REGNR_NA  = CASE WHEN :IN_TYPE_FLAG  = 'R' THEN 0 END) OR
 (REGNR_NA  <> CASE WHEN :IN_TYPE_FLAG  = 'S' THEN 0 END) OR
 (REGNR_NA  = CASE WHEN :IN_TYPE_FLAG  = 'A' THEN REGNR_NA END))


I think its much simpler to use the following.
A
Code:
ND (( :IN_TYPE_FLAG = 'R'  AND REGNR_NA = 0) OR ( :IN_TYPE_FLAG = 'S'  AND REGNR_NA <> 0))
Back to top
View user's profile Send private message
prgaj1

New User


Joined: 13 Jul 2007
Posts: 18
Location: Pennsylvania

PostPosted: Wed Mar 17, 2010 4:58 pm    Post subject: Reply to: CASE statement within WHERE clause
Reply with quote

Thank you very much - I agree it would have easy doing it as one usually do it but the SA (system analyst) wanted us to use a CASE statement... thanks again icon_biggrin.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Mar 17, 2010 8:38 pm    Post subject:
Reply with quote

Hello,

Quote:
but the SA (system analyst) wanted us to use a CASE statement...
Suggest it would be good to know why the SA wanted to use CASE. . .
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Mar 18, 2010 7:48 pm    Post subject:
Reply with quote

a criteria
REGNA_SZ = xxxxxxxxxxxxxxxxx
with xxxxxxxx being something that can be evaluated/calculated once
is Stage 1 and indexable.
most criteria with ORs are stage 2 and not indexable

So maybe the SA has a reason to ask for this.
a possiblilty is
REGNA_SZ between :hvmin and :hvmax
with (hvmin,hvmax) being (0,0), (1,999999) or (0,999999) depending on :type-flag
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 ROUNDED Problem with COMPUTE statement shalem COBOL Programming 11 Thu Feb 09, 2017 8:16 pm
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm
No new posts Summing on Mixed Case Literals Roy Ware SYNCSORT 3 Tue Jan 03, 2017 10:18 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts COBOL DB2 - CALL statement - high CPU... TS70363 DB2 15 Sun Sep 11, 2016 6:07 am


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