471,049 Members | 1,530 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,049 software developers and data experts.

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 2203
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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.