Hi,
I have been pulling out my hair since morning on this thing. So any help will be deeply appreciated.

Environment : DB2 V7.1 on OS/390

I have 3 queries in a program one after the other:

Query A:
SELECT COUNT(*)
INTO :HV1
FROM TAB1
WHERE COL1 = 1111 AND
COL2 = 'F'

Query B:
SELECT COUNT(*)
INTO :HV2
FROM TAB1
WHERE COL1 = 1111 AND
COL2 = 'X'

Query C:
SELECT COUNT(*)
INTO :HV3
FROM TAB1
WHERE COL1 = 1111 AND
COL2 = 'U'

(Please note that the only difference between the 3 queries is the different values passed for COL2).

And after this, there is a check

IF :HV1 > 0 AND
:HV2 > 0 AND
:HV3 > 0 THEN
do some processing..

So basically what is happening here is that an existence check is done. I want to get rid of these 3 sqls and roll them into one (to reduce the I/O). I know how to tune this separately(still retaining 3 sqls), but the things is I want to combine these 3 into 1. And I want the processing to be done ONLY if there is a value in TAB1 for X,U AND F !!

I tried a lot of things and this is the latest in the series:

SELECT 'F'
FROM TAB1
WHERE COL1 = 1111
AND CASE COL2
WHEN 'X' THEN 1
WHEN 'F' THEN 1
WHEN 'U' THEN 1
END IS NULL
WITH UR
FETCH FIRST 1 ROW ONLY

This gives me a result 'F', but I am not sure whether that is correct. Does CASE process all the conditions or will it exit when the first condition is met ?? I want it to process all the conditions. I am not even sure whether a CASE can be used in context or not.

I have gone totally nuts over this. HELP ..any Qs, please ask !!

Thanks
Sreejith

please note that I am NOT looking for an IN clause.

e.g : IN ('X','F','U')

This will only act as an OR. I want results when COL2 has values in the table for X AND F AND U.

Thanks..

You can download Grahame Birchall's 'SQL Cookbook' .. You are likely to find a solution ... The link is in 'Useful DB2 Stuff' thread

Cheers
Sathyaram

I have Graeme Birchall's SQL Cookbook..infact whatever I came up with was based on what I found there ..

But there is also a problem that the cookbook is for UDB and some of them might not work for OS/390.

Thanks

what's wrong with IN lists?

try this:

rudy
http://r937.com/