By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

CASE statement in subquery

Uncle Dickie
P: 67
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

Expand|Select|Wrap|Line Numbers
  1. SELECT        pa.PartNumber
  2.             ,pa.OnHandStockLevel
  3.             ,(SELECT  sum(pod.QuantityPurchased - pod.QuantityReceived)
  4.               FROM    Purchase.PurchaseOrderDetails pod
  5.               WHERE   pod.ReceiptStatusID NOT IN (3,4)
  6.               AND     pod.PartID = pa.PartID
  7.               AND     pod.ReceiptDate < (getdate()+60)) - 
  8.              (SELECT  sum(sod.QuantityOrdered - sod.QuantityDespatched)
  9.               FROM    Sales.SalesOrderDetails sod
  10.               WHERE   sod.DespatchStatusID NOT IN (3,2)
  11.               AND     sod.PartID = pa.PartID
  12.               AND     sod.OnHold = 0
  13.               AND     sod.DespatchDate < (getdate()+60)) 
  14. FROM        Structure.Parts pa
  15.  
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?
Feb 4 '09 #1
Share this Question
Share on Google+
1 Reply


Uncle Dickie
P: 67
Found a solution ISNULL works a treat so just ignore me!

Expand|Select|Wrap|Line Numbers
  1. SELECT        pa.PartNumber
  2.             ,pa.OnHandStockLevel
  3.             ,ISNULL((SELECT  sum(pod.QuantityPurchased - pod.QuantityReceived)
  4.               FROM    Purchase.PurchaseOrderDetails pod
  5.               WHERE   pod.ReceiptStatusID NOT IN (3,4)
  6.               AND     pod.PartID = pa.PartID
  7.               AND     pod.ReceiptDate < (getdate()+60)),0) - 
  8.              ISNULL((SELECT  sum(sod.QuantityOrdered - sod.QuantityDespatched)
  9.               FROM    Sales.SalesOrderDetails sod
  10.               WHERE   sod.DespatchStatusID NOT IN (3,2)
  11.               AND     sod.PartID = pa.PartID
  12.               AND     sod.OnHold = 0
  13.               AND     sod.DespatchDate < (getdate()+60)),0) 
  14. FROM        Structure.Parts pa
  15.  
Feb 4 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.