To convert Sql with case clause to Hql Code Answer

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.

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.

Related Posts

Tutorial Guruji