SQL119 with JDBC parameter markers in GROUP BY

[Gregor Kovač]

Hi!

Consider this SQL:
SELECT CASE WHEN ? = 1 THEN DUMMY ELSE DUMMY END
FROM SYSIBM.DUAL
GROUP BY CASE WHEN ? = 1 THEN DUMMY ELSE DUMMY END

If I run it via JDBC PreparedStatement and set first and second parameter to some integer value I get this error:
[Error Code: -119, SQL State: 42803] An expression starting with "DUMMY" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified.. SQLCODE=-119, SQLSTATE=42803, DRIVER=4.13.127. 2) [Error Code: -727, SQL State: 56098] An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-119", SQLSTATE "42803" and message tokens "DUMMY".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.13.127

This SQL works:
SELECT CASE WHEN 1 = 1 THEN DUMMY ELSE DUMMY END
FROM SYSIBM.DUAL
GROUP BY CASE WHEN 1 = 1 THEN DUMMY ELSE DUMMY END

What is going on?

Best regards,
Kovi

[Lennart Jonsson]

It doesn't like the parameter marker in the group by clause. I assume
this is because there is no guarantee that parameter 1 and 2 are the
same. Can you rewrite as below?

select case when x = 1 then dummy else dummy end
from (
select cast(? as int) as x, dummy from SYSIBM.DUAL
) t
group by case when x = 1 then dummy else dummy end


/Lennart

[Lennart Jonsson]

On 09/09/2013 02:45 PM, Lennart Jonsson wrote:
[...]

>
> It doesn't like the parameter marker in the group by clause. I assume
> this is because there is no guarantee that parameter 1 and 2 are the
> same.

To elaborate a bit, I believe that DB2 implements the strict behaviour
for the GROUP BY clause described in SQL92. SQL99 loosens this
restriction by allowing us to leave out columns from the GROUP BY clause
that is functionally dependent of those specified. Not sure if any other
DBMS implements this semantic, but I'm pretty sure DB2 does not.


/Lennart

[...]

[Gregor Kovač]

Dne ponedeljek, 09. september 2013 10:07:31 UTC+2 je oseba Gregor Kovač napisala:

Rewriting the SQL like this works.

Thanks.