0

I have a sql query 

    select * from user_projects where endDate is not null and startDate is not null and soft_delete=? and startDate<=? and 
endDate>=? and endDate<=? and (endDate,user_id) in (select MAX(endDate),user_id from user_projects  
where soft_delete=? group by user_id)

whose hibernate criteria was like

DetachedCriteria maxDate=DetachedCriteria.forClass(Staffing.class);
        maxDate.add(Restrictions.eq("softDelete", false));
        maxDate.setProjection(Projections.projectionList().add(Projections.groupProperty("user").as("user")).add(Projections.max("endDate"),"maxDate"));
        Criteria criteria = getCriteria();

        criteria.add(Restrictions.isNotNull("startDate")).add(Restrictions.le("startDate", new Date()));
        criteria.add(Restrictions.isNotNull("endDate")).add(Restrictions.ge("endDate", new Date())).add(Restrictions.le("endDate", date));
        criteria.add(Restrictions.eq("softDelete", false));
        criteria.add(Subqueries.propertiesIn(new String[]{"user","endDate"}, maxDate));
        staffing = criteria.list();

Now I have updated my query to 

select * from user_projects where endDate is not null and startDate is not null and soft_delete=? and startDate<=? and 
    endDate>=? and endDate<=? and (user_id,endDate) in (select user_id ,
    CASE WHEN MAX(CASE WHEN enddate IS NULL THEN 1 ELSE 0 END) = 0
            THEN MAX(enddate)
       END
    from user_projects where soft_delete=0 group by user_id)

Now I am stuck with this CASE WHEN .Any suggestion how to modify my hibernate criteria for the updated sql .I have read that hibernate not support CASE WHEN so is there any other way to solve this issue. Thanks in advance !!!

Tanvi Jain
  • 113
  • 1
  • 14
  • https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_coalesce – Ravinder Reddy Oct 13 '17 at 08:33
  • The solution is just opposite of what I am doing. For me if endDate is null...it mean it is max endDate but as max fuction always return value and ignore null value thats why I have used case when... Try to compare my situation with this post https://stackoverflow.com/questions/21286215/how-can-i-include-null-values-in-a-min-or-max .................Hope now you will understand what I am trying to do – Tanvi Jain Oct 13 '17 at 08:54

0 Answers0