Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    1

    Unanswered: DB2 Case Statement Assistance

    Hi, I am trying to code a complex case statement which is failing on a returning more than one row error. Can anyone see what is wrong?


    SELECT
    CASE
    WHEN ( SELECT COUNT(ID) FROM MEASURE A, VARIABLES B
    WHERE B.VARIABLE = 11
    AND A.CODE = B.CODE
    AND B.UNIT = 'feet'
    AND A.SAMPLE_SK = 23) = 1
    THEN
    ( SELECT FLAG FROM MEASURE A, VARIABLES B
    WHERE A.SAMPLE = 23
    AND A.CODE = B.CODE
    AND B.UNIT = 'feet'
    AND B.VARIABLE = 11 )
    ELSE
    ( SELECT FLAG FROM MEASURE A, VARIABLES B
    WHERE A.SAMPLE = 23
    AND A.CODE = B.CODE
    AND B.UNIT = 'feet'
    AND A.CODE = 15114
    AND B.VARIABLE = 11 )
    END CASE
    FROM SADA.DUAL

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: DB2 Case Statement Assistance

    The SELECT in either THEN part or ELSE part is returning more than one row .... Have you tried them on their own ?

    Cheers
    Sathyaram

    Originally posted by emmelt
    Hi, I am trying to code a complex case statement which is failing on a returning more than one row error. Can anyone see what is wrong?


    SELECT
    CASE
    WHEN ( SELECT COUNT(ID) FROM MEASURE A, VARIABLES B
    WHERE B.VARIABLE = 11
    AND A.CODE = B.CODE
    AND B.UNIT = 'feet'
    AND A.SAMPLE_SK = 23) = 1
    THEN
    ( SELECT FLAG FROM MEASURE A, VARIABLES B
    WHERE A.SAMPLE = 23
    AND A.CODE = B.CODE
    AND B.UNIT = 'feet'
    AND B.VARIABLE = 11 )
    ELSE
    ( SELECT FLAG FROM MEASURE A, VARIABLES B
    WHERE A.SAMPLE = 23
    AND A.CODE = B.CODE
    AND B.UNIT = 'feet'
    AND A.CODE = 15114
    AND B.VARIABLE = 11 )
    END CASE
    FROM SADA.DUAL
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Mar 2004
    Location
    india
    Posts
    24

    Re: DB2 Case Statement Assistance

    SELECT in ELSE part must be giving more than one row. coz when COUNT(ID) is not equal to 1, then only ELSE part is TRUE. Otherwise one row will return. Check the WHERE CLAUSE in ELSE case.



    Originally posted by sathyaram_s
    The SELECT in either THEN part or ELSE part is returning more than one row .... Have you tried them on their own ?

    Cheers
    Sathyaram

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    It seems that CASE...WHEN...THEN is not a *true* if-then statement, there's no guarantee the sub-queries won't be executed even if the evaluation says they shouldn't.

    Try this test case. The first query returns three rows with B, with A never returned, but if you switch A with a query, it gets the SQL0811N, even though that sub-select should not be evaluated.

    DROP TABLE CASE_SUB;
    CREATE TABLE CASE_SUB (
    A CHAR(1),
    B CHAR(1));

    INSERT INTO CASE_SUB VALUES ('z','1');
    INSERT INTO CASE_SUB VALUES ('z','2');
    INSERT INTO CASE_SUB VALUES ('x','1');

    SELECT
    CASE
    WHEN (SELECT COUNT(A) from case_sub
    WHERE A='z') = 1
    THEN 'A'
    ELSE 'B'
    END CASE
    FROM CASE_SUB;

    SELECT
    CASE
    WHEN (SELECT COUNT(A) from case_sub
    WHERE A='z') = 1
    THEN
    (select B from CASE_SUB
    WHERE A='z')
    ELSE 'B'
    END CASE
    FROM CASE_SUB;

    I'd imagine this means the optimizer is re-writing things to be more efficient, but the result isn't inuitive.
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    hmm .. Quiet interesting ... Did you have a chance to see what the optimized SQL looks like ?

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    I tried to do a bit of formatting.

    SELECT
    CASE
    WHEN (Q5.$C0 = 1)
    THEN $INTERNAL_FUNC$((SELECT Q1.B
    FROM PETRUK.CASE_SUB AS Q1
    WHERE (Q1.A = 'z')), 1)
    ELSE 'B'
    END AS "CASE"
    FROM
    (SELECT COUNT(Q4.$C0)
    FROM
    (SELECT 'z'
    FROM PETRUK.CASE_SUB AS Q3
    WHERE (Q3.A = 'z')) AS Q4) AS Q5,
    PETRUK.CASE_SUB AS Q6
    --
    Jonathan Petruk
    DB2 Database Consultant

  7. #7
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Originally posted by J Petruk
    I tried to do a bit of formatting.

    SELECT
    CASE
    WHEN (Q5.$C0 = 1)
    THEN $INTERNAL_FUNC$((SELECT Q1.B
    FROM PETRUK.CASE_SUB AS Q1
    WHERE (Q1.A = 'z')), 1)
    ELSE 'B'
    END AS "CASE"
    FROM
    (SELECT COUNT(Q4.$C0)
    FROM
    (SELECT 'z'
    FROM PETRUK.CASE_SUB AS Q3
    WHERE (Q3.A = 'z')) AS Q4) AS Q5,
    PETRUK.CASE_SUB AS Q6
    BTW, as a work-around, just add FETCH FIRST ROW ONLY to the sub-select:

    SELECT
    CASE
    WHEN (SELECT COUNT(A) from case_sub
    WHERE A='z') = 1
    THEN
    (select B from CASE_SUB
    WHERE A='z' fetch first row only)
    ELSE 'B'
    END CASE
    FROM CASE_SUB;
    --
    Jonathan Petruk
    DB2 Database Consultant

  8. #8
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    I'm not sure it's the re-writing that's the issue... it's still the CASE execution.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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