Reputation: 115773

Using a CASE statement in HQL select

Is there any way to do the following in HQL:

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

Upvotes: 23

Answers (7)

Ruben

Reputation: 9120

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

Upvotes: 6

Martin

Reputation:

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

Upvotes: -2

Faiz Akram

Reputation: 558

I facing the same problem in HQL then I solved the following query is

select CONCAT(event.address1,', ', CASE WHEN event.address2 IS NULL THEN '' ELSE concat(event.address2,', ') END, event.city from EventDetail event where event.startDate>=:startDate and event.endDate<=:endDate;

Upvotes: 1

V H

Reputation: 8587

We use hibernate HQL query extensively and I think finally there is a hackish way of doing such a thing :

Assuming we originally had a query of

i2.element.id = :someId

Then decided to expand this to be something like this:

((i.element.id = :someId and i2.element.id=:someId) or (i2.element.id = :someId))

But there was an issue where we want it to only lookup for this based on classType so a case statement:

(case when type(i)=Item then 
((i.element.id = :someId and i2.element.id=:someId) or (i2.element.id = :someId))
else
i.element.id = :someId
end)

Above will not work you could make an easy version of above work by doing:

(case when type(i)=Item then 
i2.element.id
else
i.element.id
end)=:elementId

But this does not actually do what we need it to do, we want it to do exact above query, so knowing you can assign a variable at the end of a case statement in there where bit of HQL:

(
                            (
                                (case when 
                                    type(r)=Item then 
                                        i.element.id 
                                    else 
                                        i.element.id end) = :elementId 
                                and 
                                (case when 
                                    type(r)=Item then 
                                        i2.element.id 
                                    else 
                                        i.element.id end) = :elementId
                            )
                            or 
                            (case when 
                                    type(r)=Item then 
                                        i2.element.id 
                                    else 
                                        i.element.id end) = :elementId 
                            )

I have managed to make the query now work based on case statement, sure it is a lot more long winded but actually does the same as the first instance

Upvotes: 0