CODEWITHSUNDEEP
hibernatenhibernatehql
Dean Strydom
Dean Strydom

Reputation: 295

Hibernate hql multiple case statement for same attribute

I am changing my sql to hql and in the sql I have this 

CASE `Leave_Type`
    WHEN 1 THEN IF(STRCMP(`Leave_Comments`,'') = 0, 'Annual Leave', concat('Annual Leave: ', `Leave_Comments`))
    WHEN 2 THEN IF(STRCMP(`Leave_Comments`,'') = 0, 'Sick Leave', concat('Sick Leave: ', `Leave_Comments`))
    WHEN 3 THEN IF(STRCMP(`Leave_Comments`,'') = 0, 'Maternity/Paternity Leave', concat('Maternity/Paternity Leave: ', `Leave_Comments`))
    WHEN 4 THEN IF(STRCMP(`Leave_Comments`,'') = 0, 'Family Leave', concat('Family Leave: ', `Leave_Comments`))
    WHEN 5 THEN IF(STRCMP(`Leave_Comments`,'') = 0, 'Study Leave', concat('Study Leave: ', `Leave_Comments`))
    WHEN 6 THEN IF(STRCMP(`Leave_Comments`,'') = 0, 'Unpaid Leave', concat('Unpaid Leave: ', `Leave_Comments`))
    WHEN 7 THEN IF(STRCMP(`Leave_Comments`,'') = 0, 'Other Leave', concat('Other Leave: ', `Leave_Comments`))
END As 'text',

I tried porting it to hql with my attempt below but I get the error

org.hibernate.hql.internal.ast.QuerySyntaxException: expecting \"end\", found 'else' near line 1, column 594

What is the correct hql equivalent for a sql like this?

HQL attempt:

(case when l.leave_Type = 1 then (case when strcmp(l.leave_Comments,'') = 0 then 'Annual Leave' else concat('Annual Leave: ', l.leave_Comments) else 
(case when l.leave_Type = 2 then (case when strcmp(l.leave_Comments,'') = 0 then 'Sick Leave' else concat('Annual Leave: ', l.leave_Comments) end) else 
(case when l.leave_Type = 3 then (case when strcmp(l.leave_Comments,'') = 0 then 'Maternity/Paternity Leave' else concat('Maternity/Paternity Leave: ', l.leave_Comments) end) else 
(case when l.leave_Type = 4 then (case when strcmp(l.leave_Comments,'') = 0 then 'Family Leave' else concat('Family Leave: ', l.leave_Comments) end) else 
(case when l.leave_Type = 5 then (case when strcmp(l.leave_Comments,'') = 0 then 'Study Leave' else concat('Study Leave: ', l.leave_Comments) end) else 
(case when l.leave_Type = 6 then (case when strcmp(l.leave_Comments,'') = 0 then 'Unpaid Leave' else concat('UnpaidLeave: ', l.leave_Comments) end) else 
(case when l.leave_Type = 7 then (case when strcmp(l.leave_Comments,'') = 0 then 'Other Leave' else concat('Other Leave: ', l.leave_Comments) end) else null end) end) end) end) end)) end) end)

Upvotes: 0

Views: 3380

Answers (1)

Ponni
Ponni

Reputation: 433

case when l.leave_Type = 1 then 
    (case when strcmp(l.leave_Comments,'') = 0 then 'Annual Leave' else concat('Annual Leave: ', l.leave_Comments)  end)
when l.leave_Type = 2 then 
    (case when strcmp(l.leave_Comments,'') = 0 then 'Sick Leave' else concat('Annual Leave: ', l.leave_Comments) end) 
when l.leave_Type = 3 then 
    (case when strcmp(l.leave_Comments,'') = 0 then 'Maternity/Paternity Leave' else concat('Maternity/Paternity Leave: ', l.leave_Comments) end) 
when l.leave_Type = 4 then 
    (case when strcmp(l.leave_Comments,'') = 0 then 'Family Leave' else concat('Family Leave: ', l.leave_Comments) end) 
when l.leave_Type = 5 then 
    (case when strcmp(l.leave_Comments,'') = 0 then 'Study Leave' else concat('Study Leave: ', l.leave_Comments) end) 
when l.leave_Type = 6 then 
    (case when strcmp(l.leave_Comments,'') = 0 then 'Unpaid Leave' else concat('UnpaidLeave: ', l.leave_Comments) end) 
when l.leave_Type = 7 then 
    (case when strcmp(l.leave_Comments,'') = 0 then 'Other Leave' else concat('Other Leave: ', l.leave_Comments) end) 
else NULL end

Upvotes: 1

Related Questions