CASE in SQL query with WHERE clause

mfguy01 · New User Joined: 06 Sep 2010 Posts: 30 Location: India

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.

Nic Clouston · Posted: Wed Jul 31, 2013 3:37 pm

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?

sushanth bobby · Posted: Wed Jul 31, 2013 3:42 pm

Hey mfguy,

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

Regards,
sushanth

mfguy01 · New User Joined: 06 Sep 2010 Posts: 30 Location: India

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.

Nic Clouston · Posted: Wed Jul 31, 2013 4:35 pm

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.

mfguy01 · New User Joined: 06 Sep 2010 Posts: 30 Location: India

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.

Akatsukami · Posted: Wed Jul 31, 2013 4:49 pm

mfguy01 · New User Joined: 06 Sep 2010 Posts: 30 Location: India

Thanks you all for your valuable time.