Newbie first Foray into CASE! 1

[AGus01]

Hi this is my first attempt at using CASE in a SQL statment. Basicly i want to total up the total of two colums [option_Price] and [Sub_option_value] and then multiply by [quantity]. Then add all the totals together into one figure.

However i dont know if i am to add or subtract the results of [option_Price] or [Sub_option_value] together this is dependent on if [item_value] value is a '-' or '+'

I get a syntax error in this statment point marked in red

Line 5: Incorrect syntax near '='. Can anyone help with this st

DECLARE @BasketTotal int

SELECT @BasketTotal = SUM(
	CASE [O].Line 5: Incorrect syntax near '='.
	WHEN [O].Item_Value [COLOR=red]=[/color] '+'
	 THEN ([O].Option_Price + Isnull([SOI].Sub_Option_Value,0)) * [B][I].Quantity 
	WHEN [O].Item_Value = '-'
	 THEN ([O].Option_Price - Isnull([SOI].Sub_Option_Value,0)) * [B][I].Quantity 
	WHEN [O].Item_Value Is null
	 THEN ([O].Option_Price * [B][I].Quantity 
	END		)
	
FROM
	[BasketItem] AS [B][I]
LEFT OUTER JOIN
	[BasketItem_SubOption] AS [BI_SO]
ON
	[B][I].[BasketItemID] = [BI_SO].[BasketItemID]
JOIN
	[tblOptions] AS [O]
ON
	[B][I].[OptionID] = [O].[Option_ID]
LEFT OUTER JOIN
	[tblSub_Option_Items] AS [SOI]
ON
	[BI_SO].[Sub_Option_Item_ID] = [SOI].[Sub_Option_Item_ID]
LEFT OUTER JOIN
	[Offer] AS [OC]
ON
	[B][I].[OfferID] = [OC].[OfferID] 
WHERE
	[B][I].[CustomerID] = '1262427'


Print @BasketTotal

 

[AGus01]

opps here is the code proper:

DECLARE @BasketTotal int

SELECT @BasketTotal = SUM(
	CASE [O].Item_Value
	WHEN [O].Item_Value = '+'
	 THEN ([O].Option_Price + Isnull([SOI].Sub_Option_Value,0)) * [B][I].Quantity 
	WHEN [O].Item_Value = '-'
	 THEN ([O].Option_Price - Isnull([SOI].Sub_Option_Value,0)) * [B][I].Quantity 
	WHEN [O].Item_Value Is null
	 THEN ([O].Option_Price * [B][I].Quantity 
	END		)
	
FROM
	[BasketItem] AS [B][I]
LEFT OUTER JOIN
	[BasketItem_SubOption] AS [BI_SO]
ON
	[B][I].[BasketItemID] = [BI_SO].[BasketItemID]
JOIN
	[tblOptions] AS [O]
ON
	[B][I].[OptionID] = [O].[Option_ID]
LEFT OUTER JOIN
	[tblSub_Option_Items] AS [SOI]
ON
	[BI_SO].[Sub_Option_Item_ID] = [SOI].[Sub_Option_Item_ID]
LEFT OUTER JOIN
	[Offer] AS [OC]
ON
	[B][I].[OfferID] = [OC].[OfferID] 
WHERE
	[B][I].[CustomerID] = '1262427'


Print @BasketTotal

 

[swampBoogie]

You have mixed the two possible variants of case expressions.

CASE WHEN O.Item_Value = '+'
     THEN (O.Option_Price + coalesce(SOI.Sub_Option_Value,0)) * BI.Quantity
    WHEN O.Item_Value = '-'
     THEN (O.Option_Price - coalesce(SOI.Sub_Option_Value,0)) * BI.Quantity
    WHEN O.Item_Value Is null
     THEN O.Option_Price * BI.Quantity
    END

 

[AGus01]

Thanks swampboogie for some reason i totaly missed your post
here is the post i was about to send:


In case anyone was intrested i got it to work. However i still dont really understand the syntax and why this works and the other does not? Anyone wish to shed some light on this?

SELECT @BasketTotal = SUM(
	CASE 
	WHEN [SOI].Item_Value = '+'
	 THEN ([O].Option_Price + Isnull([SOI].Sub_Option_Value,0)) * [B][I].Quantity 
	WHEN [SOI].Item_Value = '-'
	 THEN ([O].Option_Price - Isnull([SOI].Sub_Option_Value,0)) * [B][I].Quantity 
	WHEN [SOI].Item_Value Is null
	 THEN [O].Option_Price * [B][I].Quantity
	END		) 
	
FROM
	[BasketItem] AS [B][I]
LEFT OUTER JOIN
	[BasketItem_SubOption] AS [BI_SO]
ON
	[B][I].[BasketItemID] = [BI_SO].[BasketItemID]
JOIN
	[tblOptions] AS [O]
ON
	[B][I].[OptionID] = [O].[Option_ID]
LEFT OUTER JOIN
	[tblSub_Option_Items] AS [SOI]
ON
	[BI_SO].[Sub_Option_Item_ID] = [SOI].[Sub_Option_Item_ID]
JOIN 		--only intrested in products which are specific to this offer
	tblProducts AS [P]
ON
	[B][I].[ProductID] = [P].[Product_ID]
JOIN
	OfferSubCategory AS [OS]
ON 
	[P].[Category_ID] = [OS].[SubCategoryID]
WHERE
	[B][I].[CustomerID] = @CustomerID
AND
	[OS].[OfferID] = @OfferID