Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of To convert Sql with case clause to Hql without wasting too much if your time.
The question is published on by Tutorial Guruji team.
The question is published on by Tutorial Guruji team.
I need to convert this sql query to hql.
Sql query:
SELECT COALESCE(SUM(t2.Ammount * CASE WHEN t2.idAccountDeb = c.id AND t2.idAccountDeb<>t2.idAccountCred THEN 1
WHEN t2.idAccountCred AND t2.idAccountDeb<>t2.idAccountCred THEN (-1) ELSE (0) END),0)
Hql query that did not work:
(select COALESCE(SUM(t2.Ammount * CASE WHEN t2.AccountDeb = c AND t2.AccountDeb<>t2.AccountCred THEN 1
WHEN t2.AccountCred AND t2.AccountDeb<>t2.AccountCred THEN (-1) ELSE (0) END),0)
AST Hibernate error:
ERROR: <AST>: 2: 24: unexpected AST node:.
Answer
This is the MySQL:
SELECT COALESCE(SUM(CASE WHEN t2.idAccountDeb t2.valor * = c.id AND t2.idAccountDeb <> 1
THEN t2.idAccountCred
WHEN AND t2.idAccountCred t2.idAccountDeb <> t2.idAccountCred
THEN (-1)
ELSE (0)
END), 0)
This code is not syntactically correct. *= is not a MySQL operator (the list is here). The second when misformed. I might guess that the intended logic is:
SELECT SUM(CASE WHEN t2.idAccountDeb = t2.valor AND t2.idAccountDeb <> 1
THEN t2.idAccountCred
WHEN t2.idAccountDeb <> t2.idAccountCred
THEN -1
ELSE 0
END)
Or something like this. This logic should work in both MySQL and HQL.
EDIT:
Something like this should work in both databases:
SELECT SUM(t2.Ammount * (CASE WHEN t2.idAccountDeb = c.id AND t2.idAccountDeb = t2.idAccountCred
THEN 1
WHEN t2.idAccountDeb = c.id AND t2.idAccountDeb <> t2.idAccountCred
THEN -1
ELSE 0
END)
)
We are here to answer your question about To convert Sql with case clause to Hql - If you find the proper solution, please don't forgot to share this with your team members.