DB2 'CASE' puzzle slqerr -811

[lenygold via DBMonster.com]

Hi everybody?
here is 2 CASE EXAMPLES:

WITH CASE_SUB(A,B) AS
(VALUES('z','1' ),
('z','2'),
('x','1'))
SELECT
CASE
WHEN (SELECT COUNT(A) from case_sub WHERE A='z') = 1
THEN 'A'
ELSE 'B'
END AS CASE
FROM CASE_SUB;

CASE
----
B
B
B

3 record(s) selected.

2ND EXAMPLE:

WITH CASE_SUB(A,B) AS
(VALUES('z','1' ),
('z','2'),
('x','1'))
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 AS CASE
FROM CASE_SUB;

sqlcode: -811 The result of a scalar fullselect, SELECT INTO statement, or
VALUES INTO statement is more than one row.
WHY ELSE 'B' is not executed?

--
Message posted via http://www.dbmonster.com

[Tonkuma]

Re: DB2 'CASE' puzzle slqerr -811

>sqlcode: -811 The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row. <<
The reson of -811 would be that the following subselect in THEN clause
retuned multiple rows for all result rows.
(select B from CASE_SUB WHERE A='z')

[lenygold via DBMonster.com]

Re: DB2 'CASE' puzzle slqerr -811

Thank you very much Tonkuma for your help.
I have learn alot from you

Tonkuma wrote:

>>sqlcode: -811 The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row. <<

>The reson of -811 would be that the following subselect in THEN clause
>retuned multiple rows for all result rows.
>(select B from CASE_SUB WHERE A='z')

--
Message posted via DBMonster.com

http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200806/1