Skip to Content
avatar image
Former Member

Isnull function is not working

Hello,

I am working on a report in which i need department wise order details for current month
report should showed all departments and although ordered qty is null for one or department

for that i am using below query

SELECT distinct T0.[OcrCode],Isnull(Sum(T0.[Quantity]) ,0) AS 'Booking As on date in Current Month', Isnull( Sum( T0.[opencreqty]) ,0) 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]

But it is not working showing only those departments which has ordered quantity

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Oct 25, 2016 at 06:25 PM

    What type of database are you connecting to and what type of connection are you using?

    For most databases, the "Sum()" function will never return null - it will default to 0. Also, when you use Sums, you'll never get duplicate values, so you don't need to use "Distinct"

    If you're using SQL Server, I might change the query to something like this:

    SELECT
    OcrCode,
    Sum(Quantity) AS 'Booking As on date in Current Month',
    Sum(OpenCreQty) As 'TOTAL PENDING'
    from (
    SELECT distinct
    T0.[OcrCode],
    IsNull(T0.[Quantity] ,0) as Quantity,
    Isnull(T0.[opencreqty] ,0) as OpenCreQty
    FROM RDR1 T0
    INNER JOIN ORDR T1 ON T0.[DocEntry] = T1.[DocEntry]
    WHERE T0.[OcrCode] not in ('Store', 'mktg', 'acct')
    and DateDiff(MM,T0.DocDate,Getdate())=0 ) as data
    GROUP BY OcrCode

    Will the RDR1 table always have a record for the department even if there are no orders? What about the ORDR table? If not, then that's what's causing the departments to not appear. In that case, you would need to have a join from whatever table contains the list of valid departments and their descriptions to RDR1 and use the fields from that table instead of RDR1 to show the department.

    -Dell

    Add comment
    10|10000 characters needed characters exceeded