Subquery within Case Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Subquery within Case Statement

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2013-10-04 : 17:36:40

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 06:09:16

do you mean this?


SELECT TOP 100 
	o.[ID]
	,CASE 
	  WHEN LEN(p.ShadowOf) > 1 THEN p.ShadowOf 
	  WHEN p.IsBundle = 1 THEN b.ChildProductId  pp.ShadowOf
	  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
OUETR APPLY (SELECT ShadowOf 
             FROM tmpdb.dbo.bvc_Product 
             WHERE ProductID=b.ChildProductId)pp
ORDER BY ShipDate desc, IsBundle

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SergioM
Posting Yak Master

170 Posts

Posted - 2013-10-07 : 14:05:26

Not quite. However, Outer Apply is a GREAT feature I had never seen. I'm glad you mentioned it. I realize my own post is confusing. I'll repost with a clearer example.

-Sergio
I use Microsoft SQL 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 23:49:17

quote:
Originally posted by SergioM

Not quite. However, Outer Apply is a GREAT feature I had never seen. I'm glad you mentioned it. I realize my own post is confusing. I'll repost with a clearer example.

-Sergio
I use Microsoft SQL 2008

see what all you can do with it

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources