468,556 Members | 2,222 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,556 developers. It's quick & easy.

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

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
1 2160
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.

Similar topics

4 posts views Thread by Don | last post: by
4 posts views Thread by Hans | last post: by
1 post views Thread by mirth | last post: by
6 posts views Thread by ryan.mclean | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by UniDue | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.