CODEWITHSUNDEEP
javasqlhibernatedb2
nkr1pt
nkr1pt

Reputation: 4666

Hibernate criteria DB2 composite keys in IN clause

Hibernate criteria, using DB2 dialect, generates the following SQL with composite keys in the IN clause, but DB2 answers that the query is incorrect:

select * from tableA where (x, y) IN ( ( 'x1', y1) )

but, DB2 throws this:

SQL0104N  An unexpected token "," was found following ", y) in (
('x1'". Expected tokens may include:  "+".  SQLSTATE=42601

Upvotes: 0

Views: 1098

Answers (1)

user327961
user327961

Reputation: 2490

Correct syntax would be

select * from tableA where (x, y) IN (VALUES ( 'x1', y1) )

Note you specify the VALUES clause only once, so a multiple rows results could be obtained with

select * from tableA where (x, y) IN (VALUES ( 'x1', y1), ( 'x2', y2) )

Don't know how to make Hibernate do it, however.

Upvotes: 1

Related Questions