0

Having clause is not working.

Oct 21, 2016 at 08:47 AM

73

Former Member

Hello All,

I am creating a report which consists all departments and sale order quantity departments wise for current month.

although quantity is 0 for any department but report should show all the departments. for that i am using below query.

SELECT distinct T0.[OcrCode],Sum(T0.[Quantity])AS 'Booking As on date in Current Month', Sum( T0.[opencreqty]) As 'TOTAL PENDING' FROM RDR1 T0 INNER JOIN ORDR T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE T0.[OcrCode] <>'Store' and T0.[OcrCode] <>'mktg' and T0.[OcrCode] <>'acct' and DateDiff(MM,T0.DocDate,Getdate())=0

GROUP BY T0.[OcrCode]

Having Sum(T0.[Quantity]) >=0

But 'having' is not working.
How is it possible?

Nagarajan K Oct 21, 2016 at 09:38 PM
1

Hi,

Please remove Distinct and then try.

Thanks

Share
Johan Hakkesteegt Oct 21, 2016 at 09:01 AM
0

Hi,

For me this query gives no results at all, so I cannot test it, but my guess is that the problem lies either in INNER JOIN RDR1 (it does not need to be there) or in the WHERE clause.

Have you checked that there is a department that coheres to the parameters in the WHERE clause and that has a Quantity of zero?

Regards,

Johan

Share
Abhilash Kumar
Oct 21, 2016 at 10:58 AM
0

Hi Ambesh,

The Having Clause filters out 'groups'.

Change the JOIN to use a LEFT JOIN and also get rid of the Having Clause:

SELECT distinct T0.[OcrCode],Sum(T0.[Quantity])AS 'Booking As on date in Current Month', Sum( T0.[opencreqty]) As 'TOTAL PENDING' FROM RDR1 T0 LEFT JOIN ORDR T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE T0.[OcrCode] <>'Store' and T0.[OcrCode] <>'mktg' and T0.[OcrCode] <>'acct' and DateDiff(MM,T0.DocDate,Getdate())=0

GROUP BY T0.[OcrCode]

-Abhilash

Share
Former Member Oct 21, 2016 at 11:58 AM
0

Hi Ambesh,

I believe the query with Having clause is correct as it gives me result if I remove Where Condition. But there seems no issue in the query.