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 in SQL query with WHERE clause

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

New User


Joined: 06 Sep 2010
Posts: 30
Location: India

PostPosted: Wed Jul 31, 2013 3:11 pm    Post subject: CASE in SQL query with WHERE clause
Reply with quote

Hi All,

I need help in a Embedded sql query where variable used in WHERE clause may contain either SPACES or some value.
In general, if all values are given,query will look like:-
EXEC SQL
SELECT COUNT(*) INTO :WS-ABC
FROM <TABLE T1>
WHERE A = :WS-A
AND B = :WS-B
AND C IN (SELECT DISTINCT(D) FROM T2)
.....
END-EXEC.

Here WS-A & WS-B may be SPACES or any values.
As per requirement, if particular value for WS-A & WS-B is given then that will be considered.
If no value given for WS-A & WS-B, then query should run for all values of both.

I was thinking to use CASE in where clause as:
WHERE
CASE WHEN WS-A <> SPACES
THEN A = :WS-A
ELSE 1=1
AND
CASE WHEN WS-B <> SPACES
THEN B = :WS-B
ELSE 1=1
....

1. I am not sure that CASE can be used with WHERE claues.
2. Any other solution can be there for this problem.

Please help.
Thanks in Advance.
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1713
Location: UK

PostPosted: Wed Jul 31, 2013 3:37 pm    Post subject:
Reply with quote

Why not do your decision making in your program which can do it quicker and submit the appropriate query so that DB2 can do only what is necessary - retrieve the data - and free up its resource that much faster?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Jul 31, 2013 3:42 pm    Post subject:
Reply with quote

Hey mfguy,

Case cannot be used like that, use programming logic, write 2 static queries and use if condition logic to decide.

Regards,
sushanth
Back to top
View user's profile Send private message
mfguy01

New User


Joined: 06 Sep 2010
Posts: 30
Location: India

PostPosted: Wed Jul 31, 2013 4:14 pm    Post subject:
Reply with quote

Hi Sushanth,

I came to use of CASE this time only and that also google search for my problem.
I am still cluseless, what needsto be done.
Pease help.
Thanks.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1713
Location: UK

PostPosted: Wed Jul 31, 2013 4:35 pm    Post subject:
Reply with quote

I do not understand why you cannot understand Sushanth's reply.
You CANNOT use CASE as you were suggesting.
You need to include logic in your program:
write 2 sql queries
if one condition exec one query
else exec the other query.
Back to top
View user's profile Send private message
mfguy01

New User


Joined: 06 Sep 2010
Posts: 30
Location: India

PostPosted: Wed Jul 31, 2013 4:44 pm    Post subject:
Reply with quote

Hi Nic,

It is program to prepare report using data from many tables.
The query I shown as example, is same for input values but count of such queries is already more than 100 so writing 2 queries each for such scenerios will take the count 200+. It is easy in writing and I can do that but considering 'too much time' consumption in this, I was looking for short path and smart logic.

Thanks.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Wed Jul 31, 2013 4:49 pm    Post subject:
Reply with quote

mfguy01 wrote:
Hi Nic,
I was looking for short path and smart logic.

Too bad, so sad. As Sushanth and Mr. Clouston have told you, there is none.
Back to top
View user's profile Send private message
mfguy01

New User


Joined: 06 Sep 2010
Posts: 30
Location: India

PostPosted: Wed Jul 31, 2013 5:05 pm    Post subject:
Reply with quote

Thanks you all for your valuable time.
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


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