SELECT CASE expression advice needed. (Transact SQL)

[VBDotNetProgrammer]

Hi all,
this is the scenario. I am using a select case statement to check the value of a field value in a particular row then depending on the value i change the value of another field in the same table on the same row. Simple enough you might think but it doesnt seem to like it.

This is the code im using does it seem right to you lot?

SELECT CASE
WHEN TotalAmount > 0 THEN BuySell = "BUY"
WHEN TotalAmount = 0 THEN BuySell = ""
ELSE TotalAmount = TotalAmount * -1
FROM #Temp2

Any ideas or advice would be greatly appreciated.

Thanks

 

[collierd]

What error is it coming back with
Is it just a case of surrounding text with single quotes
and putting an end at the end of the case statement, i.e. next line after -1

Damian

 

[VBDotNetProgrammer]

Ive inserted an end and given it a label, im now trying to insert the created field into a temp table but it is saying the field name ive given it is invalid.
This is the code now

SELECT Bby,
TotalAmount,
USDEquivalent,
CASE
WHEN TotalAmount > 0 THEN 'BUY'
WHEN TotalAmount < 0 THEN 'SELL'
WHEN TotalAmount = 0 THEN ''
END 'Buy / Sell'
INTO #Temp3
FROM #Temp2

its saying &quot;Buy / Sell not a valid identifier&quot; i would have thought the INTO command would create that column in the new table?

 

[collierd]

needs to be as 'Buy / Sell'
i.e.

END as 'Buy / Sell'