SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Subquery within Case Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SergioM
Posting Yak Master

168 Posts

Posted - 10/04/2013 :  17:36:40  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 10/07/2013 :  06:09:16  Show Profile  Reply with Quote
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
Go to Top of Page

SergioM
Posting Yak Master

168 Posts

Posted - 10/07/2013 :  14:05:26  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/07/2013 :  23:49:17  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000