HQL: select statement along with using 'case when then' giving unexpected token error

Question

I am facing an issue in writing a Hql Query.

I have a mysql table named MyTable with column names Id , Column1,Column2 and Type. All fields are integer type.

The select query is based on the value in 'Type' column . If the value in 'Type' column is 0 then the select query based value in Column1. If value in type column is 1 then select query will be based on value in column2.

I have written the query using 'case when then' is SQL successfully. But the same query giving exception when using as a Hql query

SQL query:

select * from  MyTable where case when Type=0 then Column1=234 when Type=1 then       Column2=564 end;

HQL query:

from MyTableObj obj where case when obj.type=0 then obj.column1=234 when obj.type=1 then obj.column2=564 end;

gives the following error,

 17:30:16,197 ERROR [PARSER] line 1:127: unexpected token: =
 17:30:16,198 ERROR [PARSER] line 1:134: unexpected token: end
 17:30:16,199 WARN  [HqlParser] processEqualityExpression() : No expression to process!
 org.hibernate.hql.ast.QuerySyntaxException: unexpected token: = near line 1, column 127 [from MyTableObj obj where case when obj.type=0 then obj.column1=234 when obj.type=1 then obj.column2=564 end]
    at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
    at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
    at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82)
    at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:281)
    at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:180)
    at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:134)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
    at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94)
    at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
    at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
    at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1650)

Thanks in Advance

@Heisenberg . Sorry I almost forgot the context. It happened 3 years before. Btw Mr.Heisenberg how is your drug business going on? :) — Dinoop paloli

Thiago Thiago · Accepted Answer · 2013-11-25T19:13:05

I did something similar, but the value of the condition is the same, the only thing that changes according to some value in the DB is the column. Here is an example:

SELECT t.* FROM table t
WHERE CASE WHEN t.column1 = 1
   THEN t.column2
   ELSE t.column3
END = 123;

This worked for me. If you want your value (123 in my example) to be dependent on a condition like the column, you can use a CASE statement for that too.

Check this example in SQL, it should work for HQL as well.

HQL: select statement along with using 'case when then' giving unexpected token error

1 Answers