Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    Massachusetts
    Posts
    27

    Unanswered: SQL problem..calling all SQL experts !!

    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
    "It is Monday morning 3:02 AM. What is your SQL response time ?"

  2. #2
    Join Date
    Sep 2003
    Location
    Massachusetts
    Posts
    27
    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..
    "It is Monday morning 3:02 AM. What is your SQL response time ?"

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Sep 2003
    Location
    Massachusetts
    Posts
    27
    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
    "It is Monday morning 3:02 AM. What is your SQL response time ?"

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's wrong with IN lists?

    try this:
    Code:
    SELECT COUNT(*) 
      FROM TAB1
     WHERE COL1 = 1111 
       AND COL2 IN ('X','F','U')
    GROUP 
        BY COL1
    HAVING COUNT(DISTINCT COL2) = 3

    rudy
    http://r937.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •