0

I am trying to get data using hibernate. My query is correct but record for particular id does not exist it still returns one record (return list size is 1) with all null values.

Below is part of code:

org.hibernate.SQLQuery query = getCurrentSession().createSQLQuery(sqlQuery);
List<?> resultList = query.list();  // resultList size is one having all null values but size should be zero

The sqlQuery I am trying is below:

SELECT SUM(table1.column1=1) as column1, 
    SUM(table1.column2=1) as column2, 
    SUM(table1.column3=1) as column3 
FROM table1 
    RIGHT JOIN table2 
      ON table1.unitid =table2.id 
    RIGHT JOIN  groupsunits 
      ON table1.unitid =groupsunits.unitid 
where table2.status = 'active' and groupsunits.group_id=415

Please share your suggestions for this issue.

mabi
  • 5,279
  • 2
  • 43
  • 78
S Singh
  • 1,403
  • 9
  • 31
  • 47
  • Can you post sqlQuery & are you querying against view or table; does it contain pk. – Nayan Wadekar Aug 19 '15 at 09:24
  • @NayanWadekar Please find query in my post – S Singh Aug 19 '15 at 09:43
  • Can you describe what you're trying to achieve with this query? The reason you see this is that `SUM` is defined to return `NULL` when summing over an empty set. Your solution depends on what you want to do. – mabi Aug 19 '15 at 10:08
  • @mabi I am trying to use conditional sum on particular column for records. Do sum of column values if only column value is 1 else skip that record. I am using this query along with required joins on tables. – S Singh Aug 19 '15 at 10:28
  • So what you actually want is a `COUNT` of rows that satisfy a specific property ("have `column1 = 1`")? – mabi Aug 19 '15 at 13:12

0 Answers0