Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1922
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: 1922
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: 1781
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
No new posts Date in where clause - Windows Karthikeyan Subbarayan DB2 9 Wed Nov 15, 2017 9:07 pm
No new posts Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 3 Tue Nov 07, 2017 8:34 pm
No new posts Query on XMITIP abdulrafi All Other Mainframe Topics 1 Wed Oct 25, 2017 6:54 pm
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm

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