Using a CASE statement in HQL select

Question

Is there any way to do the following in HQL:

SELECT 
    case when flag = true then SUM(col1) else SUM(col2)
FROM 
    myTable

Answer 1

I guess you can [inline edit] ...for where-clauses:

"Simple" case, case ... when ... then ... else ... end, and "searched" case, case when ... then ... else ... end

Answer 2

Apparently the ability to do this was added in 3.0.4, with the limitation that you cannot use sub-selects in the else clause.

Answer 3

Below you can find a working query (hibernate on postgresql) that uses 2 case statements to replace a boolean value with the corresponding textual representation.

SELECT CASE ps.open WHEN true THEN 'OPEN' else 'CLOSED' END, CASE ps.full WHEN true THEN 'FULL' else 'FREE' END, ps.availableCapacity FROM ParkingState as ps

Answer 4

This is not working. Following exception is occuring :-

"undefined alias or unknown mapping: case [ select SCTBills.Id ,SCTBills.RABillNo,..."

Answer 5

This is an example using a string comparison in the condition:

SELECT CASE f.type WHEN 'REMOVE' THEN f.previousLocation ELSE f.currentLocation END FROM FileOperation f

Answer 6

See Hibernate-Forum: https://forum.hibernate.org/viewtopic.php?t=942197

Answer from Team (Gavin): case is supported in the where clause, but not in the select clause in HB3.

And seen in JIRA with State "Unresolved".