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

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

Cheers
Sathyaram

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.

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.

hmm .. Quiet interesting ... Did you have a chance to see what the optimized SQL looks like ?

Cheers
Sathyaram

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;

I'm not sure it's the re-writing that's the issue... it's still the CASE execution.