CODEWITHSUNDEEP
jpacasejpql
Pascal Kesseli
Pascal Kesseli

Reputation: 1670

JPQL: "where" clause on "case" variable

The following JPQL query runs without issues:

SELECT t, CASE WHEN (e.language.language = 'en' AND e.language.country = 'GB' AND e.language.variant = '') THEN e.text ELSE '' END sortProperty FROM Glossary g JOIN g.terms t JOIN t.expressions e ORDER BY sortProperty

whereas the following fails:

SELECT t, CASE WHEN (e.language.language = 'en' AND e.language.country = 'GB' AND e.language.variant = '') THEN e.text ELSE '' END sortProperty FROM Glossary g JOIN g.terms t JOIN t.expressions e WHERE sortProperty = 'asdf' ORDER BY sortProperty

With this exception:

Caused by: java.lang.IllegalArgumentException: Invalid unbound variable "sortproperty" in query.

Is this a limitation in the query syntax and can this somehow be circumvented (perhaps using the Criteria API)?

Thanks and best regards, Pascal

Upvotes: 1

Views: 1973

Answers (1)

Mikko Maunu
Mikko Maunu

Reputation: 42114

Problem does not have direct connection to CASE expression, in general result variables (sortProperty in this case) cannot be used in WHERE clause. That's why also following is invalid JPQL:

SELECT u.firstName AS someVar FROM User u WHERE someVar = 'blaa'

Criteria API does not help here. Similar limitation exists also in SQL:

SELECT u.first_name as someAlias 
FROM users u 
-- someAlias is not known here:
WHERE someAlias = 'blaa'

Whole condition should be pushed to WHERE clause.

Upvotes: 3

Related Questions