Hibernate query not returning Zero value

Question

Same query working in SQL

Select count(tbl_leads.lead_Status_Id) , tbl_LeadStatus.LeadStatus_Type FROM tbl_LeadStatus LEFT JOIN tbl_leads ON tbl_LeadStatus.LeadStatus_id = tbl_leads.lead_Status_Id GROUP BY tbl_LeadStatus.LeadStatus_Type;

Hibernate Query

Select s.LeadStatus_Type, count(l.status) FROM Status s "
                + "LEFT JOIN Lead l ON l.status = s.LeadStatus_id "
                + "GROUP BY s.LeadStatus_Type"

Expecting output is this

   Count  LeadStatus_Type
    '0'   'Cancelled' 
    '0'   'In-Progress' 
    '1'   'New' 
    '0'   'Sold' 
    '0'   'UnAssigned'

And HQL return this

'1', 'New'

is LeadStatus_type column is nullable? And if so, we expecting count on it from the hibernate explicitely? — Rizwan
– Rizwan, Commented Jan 2, 2018 at 7:06
'0', 'Cancelled' '0', 'In-Progress' '1', 'New' '0', 'Sold' '0', 'UnAssigned' I need this and I get this 1 "New" because there is another count is null — Harpreet Singh
– Harpreet Singh, Commented Jan 2, 2018 at 7:08
Please elaborate the above '0', 'Cancelled' '0',...? And 'UnAssigned' is special marker value representing NULL; in that case you can use the coalesc function in HQL in select part as coalesc(s.LeadStatus_Type, 'UnAssigned') — Rizwan
– Rizwan, Commented Jan 2, 2018 at 7:12
I need the 0 count when no leads found to LeadStatus_id . it not showing that record in result — Harpreet Singh
– Harpreet Singh, Commented Jan 2, 2018 at 7:18

Tim Biegeleisen · Accepted Answer · 2018-01-02 06:50:26Z

1

Your join condition looks off. In HQL we join from an entity to the other entity which exists as a property of the first entity. Most importantly, there is no ON clause as that relationship is already known within Hibernate. Try the following:

SELECT s.LeadStatus_Type, COUNT(l.status)
FROM Status s
LEFT JOIN s.LeadStatus_id l
GROUP BY s.LeadStatus_Type

edited Jan 2, 2018 at 6:50

answered Jan 2, 2018 at 6:12

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Harpreet Singh Over a year ago

Not working! Null pointer Exception and also you don't define l

Tim Biegeleisen Over a year ago

@HarpreetSingh The query in your question is raw SQL, not HQL. Are you looking for HQL or for raw SQL?

Harpreet Singh Over a year ago

SQL query is running fine and give the desired output. I Need HQL query which returns 0 if count not found. The HQL query is also running but null value count not showing in it

Harpreet Singh Over a year ago

Any Idea about this?

Tim Biegeleisen Over a year ago

The query should be working. Tell me the error and/or the error output.

|

Collectives™ on Stack Overflow

Hibernate query not returning Zero value

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related