In this query the product table tells me if an item is a bundle. Which basically means that the ID relates to a series of other ProductIDs found in the bvc_Product_Bundle table. I'm good up until I get to the CASE statement.
- The logic in the first WHEN clause tells me that if something is written in ShadowOf, that thing is the ProductID.
- The second WHEN clause asks if IsBundle is set to 1. If so, it tells me that there are actually many ProductIDs.
Now, I need to make the logic recursive & I am stuck. One of the bundled products might be a shadow.
SELECT TOP 100
o.[ID]
,CASE
WHEN LEN(p.ShadowOf) > 1 THEN p.ShadowOf
WHEN p.IsBundle = 1 THEN b.ChildProductId
ELSE oi.ProductID
END AS 'Product'
,oi.ProductID
,o.GrandTotal
,o.ShipDate
,b.ChildProductId
,ISNULL(p.IsBundle,0) AS 'Bundle'
,p.ShadowOf
,oi.Qty
FROM [SC].[dbo].[bvc_Order] o
INNER JOIN tmpdb.dbo.bvc_OrderItem oi
ON o.ID = oi.OrderID
FULL JOIN tmpdb.dbo.bvc_Product p
ON p.ID = oi.ProductID
FULL JOIN tmpdb.dbo.bvc_Product_Bundle b
ON b.ProductId = oi.ProductID
ORDER BY ShipDate desc, IsBundle
I want to do something like the following. It obviously does not work because b.ChildProductId is a field that exists within the main query, not the subquery. Is there a way to pass that information in? Maybe through a variable?
WHEN p.IsBundle = 1 THEN (SELECT ShadowOf FROM tmpdb.dbo.bvc_Product WHERE ProductID=b.ChildProductId)
-Sergio
I use Microsoft SQL 2008
Subquery within Case Statement
Posted - 2013-10-04 : 17:36:40
