Skip to Content
avatar image
Former Member

Having clause is not working.

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?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Oct 21, 2016 at 09:38 PM

    Hi,

    Please remove Distinct and then try.

    Thanks

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 21, 2016 at 09:01 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 21, 2016 at 10:58 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 21, 2016 at 11:58 AM

    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.

    Please check again.

    Thanks,

    Rahul

    Add comment
    10|10000 characters needed characters exceeded