Hi,

CASE gives wrong results when used along with a parameterized query. Here is
a simple test case....

CREATE TABLE TEST (
ID INTEGER NOT NULL PRIMARY KEY,
CODE INTEGER NOT NULL,
AMOUNT DOUBLE PRECISION NOT NULL
)

INSERT INTO TEST VALUES (1, 1, 500)
INSERT INTO TEST VALUES (2, 1, 300)
INSERT INTO TEST VALUES (3, 2, 10)
INSERT INTO TEST VALUES (4, 2, 20)

SELECT
SUM(CASE WHEN CODE = 1 THEN AMOUNT ELSE 0 END) AS SUM1,
SUM(CASE WHEN CODE = 2 THEN AMOUNT ELSE 0 END) AS SUM2
FROM
TEST

SUM1 SUM2
---- ----
800 30

Query above works as expected. When I parameterize the query as shown below,
and set parameter values as "CODE1 = 1", "CODE2 = 2"

SELECT
SUM(CASE WHEN CODE = :CODE1 THEN AMOUNT ELSE 0 END) AS SUM1,
SUM(CASE WHEN CODE = :CODE2 THEN AMOUNT ELSE 0 END) AS SUM2
FROM
TEST

SUM1 SUM2
---- ----
800 800

and increasing the number of case statements....

SELECT
SUM(CASE WHEN CODE = :CODE1 THEN AMOUNT ELSE 0 END) AS SUM1,
SUM(CASE WHEN CODE = :CODE2 THEN AMOUNT ELSE 0 END) AS SUM2,
SUM(CASE WHEN CODE = :CODE3 THEN AMOUNT ELSE 0 END) AS SUM3
FROM
TEST

CODE1 = 1, CODE2 = 2, CODE3 = 99999999

SUM1 SUM2 SUM3
---- ---- ----
800 800 800

It seems that all CASE statements after SUM1, use the first parameter "1".

Tested with Firebird 1.5.0 and Firebird 1.5.1

Tested queries with IBExpert and directly using an application developed in
Delphi+UIB. So it shouldn't be caused by parameter passing in IBExpert or
UIB.

One last note... This works as expected too:

SELECT
SUM(CASE WHEN CODE = :CODE1 THEN AMOUNT ELSE 0 END) AS SUM1,
SUM(CASE WHEN CODE = :CODE2 THEN AMOUNT / 2 ELSE 0 END) AS SUM2
FROM
TEST

CODE1 = 1, CODE2 = 2

SUM1 SUM2
---- ----
800 15

So, i bet there is something wrong in the query preparing/optimization
stage...

Volkan Ceylan





[Non-text portions of this message have been removed]