CASE statement in subquery

[Uncle Dickie]

Hi All,

I have the following bit of code which looks at a parts list and then checks on incoming and outgoing movements in the next 60 days

SELECT        pa.PartNumber
            ,pa.OnHandStockLevel
            ,(SELECT  sum(pod.QuantityPurchased - pod.QuantityReceived)
              FROM    Purchase.PurchaseOrderDetails pod
              WHERE   pod.ReceiptStatusID NOT IN (3,4)
              AND     pod.PartID = pa.PartID
              AND     pod.ReceiptDate < (getdate()+60)) - 
             (SELECT  sum(sod.QuantityOrdered - sod.QuantityDespatched)
              FROM    Sales.SalesOrderDetails sod
              WHERE   sod.DespatchStatusID NOT IN (3,2)
              AND     sod.PartID = pa.PartID
              AND     sod.OnHold = 0
              AND     sod.DespatchDate < (getdate()+60)) 
FROM        Structure.Parts pa
 

This does pretty much what I want it to do except that there may well be no purchases or sales in the time frame.

In this case in need the result of each of the sub queries to be 0 rather than 'NULL' so that the calculation of purchase - sales will still work.

I have tried using a CASE statement but it returns an error each time.

Any suggestions?

[Uncle Dickie]

Found a solution ISNULL works a treat so just ignore me!

SELECT        pa.PartNumber
            ,pa.OnHandStockLevel
            ,ISNULL((SELECT  sum(pod.QuantityPurchased - pod.QuantityReceived)
              FROM    Purchase.PurchaseOrderDetails pod
              WHERE   pod.ReceiptStatusID NOT IN (3,4)
              AND     pod.PartID = pa.PartID
              AND     pod.ReceiptDate < (getdate()+60)),0) - 
             ISNULL((SELECT  sum(sod.QuantityOrdered - sod.QuantityDespatched)
              FROM    Sales.SalesOrderDetails sod
              WHERE   sod.DespatchStatusID NOT IN (3,2)
              AND     sod.PartID = pa.PartID
              AND     sod.OnHold = 0
              AND     sod.DespatchDate < (getdate()+60)),0) 
FROM        Structure.Parts pa