extend with additional CASE - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

extend with additional CASE

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-04-30 : 09:23:49

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

558 Posts

Posted - 2013-04-30 : 09:59:18

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-30 : 12:39:10

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.

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-05-01 : 01:32:12

My mistake, a typo thats now fixed and it works, thanks for the hint

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources