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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 extend with additional CASE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Aged Yak Warrior

509 Posts

Posted - 04/30/2013 :  09:23:49  Show Profile  Reply with Quote
Hi

I have this condition in a Query...





SUM(CASE WHEN dbo.tbl_ShoppingCart.IsPriceRangeValue = 'true' 
		THEN dbo.tbl_ShoppingCart.PriceSubTotal 
		ELSE dbo.tbl_ShoppingCart.Price * dbo.tbl_ShoppingCart.Quantity
    END) 
AS SubTotal



This is fine but there are 3 other columns that now needs to be added to all this, they are...

dbo.tbl_ShoppingCart.NameTagEnabled = bit
dbo.tbl_ShoppingCart.NameTag = nVarChar(50)
dbo.tbl_ShoppingCart.NameTagPrice = Decimal(18,2)



I need to add Another condition here, if the dbo.tbl_ShoppingCart.NameTagEnabled equals to 'true' and the dbo.tbl_ShoppingCart.NameTag Is not null and <> '', then both conditions should add the dbo.tbl_ShoppingCart.NameTagPrice value.
So in the first condition it should be (dbo.tbl_ShoppingCart.PriceSubTotal + dbo.tbl_ShoppingCart.NameTagPrice) And in the other condition it should be ((dbo.tbl_ShoppingCart.Price * dbo.tbl_ShoppingCart.Quantity) + dbo.tbl_ShoppingCart.NameTagPrice)



How would I do this?

magmo
Aged Yak Warrior

509 Posts

Posted - 04/30/2013 :  09:59:18  Show Profile  Reply with Quote
I tried this approach...




					SUM(
						CASE WHEN dbo.tbl_ShoppingCart.NameTagEnabled = 'true' AND dbo.tbl_ShoppingCart.NameTagEnabled.NameTag <> '' THEN

							CASE WHEN dbo.tbl_ShoppingCart.IsPriceRangeValue = 'true'	THEN 
								dbo.tbl_ShoppingCart.PriceSubTotal + dbo.tbl_ShoppingCart.NameTagEnabled.NameTagPrice 
							ELSE 
								(dbo.tbl_ShoppingCart.Price * dbo.tbl_ShoppingCart.Quantity) +  + dbo.tbl_ShoppingCart.NameTagEnabled.NameTagPrice
							END
  
						ELSE

							CASE WHEN dbo.tbl_ShoppingCart.IsPriceRangeValue = 'true'	THEN 
								dbo.tbl_ShoppingCart.PriceSubTotal 
							ELSE 
								dbo.tbl_ShoppingCart.Price * dbo.tbl_ShoppingCart.Quantity
							END

						END
							) 
								AS SubTotal,



But then I get a bunch of The multi-part identifier "dbo.tbl_ShoppingCart.NameTagEnabled.NameTag" could not be bound. errors

Edited by - magmo on 05/01/2013 01:32:40
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 04/30/2013 :  12:39:10  Show Profile  Reply with Quote
Are you familar with 4-part naming? That equates to: ServerName.DatabaseName.DatabaseOwner.TableName

You are using "dbo.tbl_ShoppingCart.NameTagEnabled.NameTag" which is telling SQL that "dbo" is the server name, etc.. If you fix that, then you are work on your logic.
Go to Top of Page

magmo
Aged Yak Warrior

509 Posts

Posted - 05/01/2013 :  01:32:12  Show Profile  Reply with Quote
My mistake, a typo thats now fixed and it works, thanks for the hint
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