CASE Query

Hi i'm trying to run a CASE, Database is TS_Positions Column is Position Type, we usually get data being -1 or 1, i would like use the Function CASE to change that in my query(easier to read) 1=being a BUY... -1=being a SELL.
For Some reason my query will NOT Work, Every other part works just not the CASE part.. Any ideas?? Query:

SELECT CASE PositionType
WHEN PositionType '1' THEN 'BUY'
WHEN PositionType '-1' THEN 'SELL' AS [BS}, CAST(TradePrice as float(20,8) )AS [Price], Quantity AS Volume,
LEFT(Contracttype,1) as KIND,
strike, expiringdate, comment, (SUBSTRING (contract+CONVERT(varchar,expiringdate),1,20)) AS [FEEDCODE]
FROM TS_Positions
WHERE (Contract LIKE 'LI%') OR
(Contract LIKE 'LK%') OR
(Contract LIKE 'LL%') OR
(Contract LIKE 'LM%')
ORDER BY ContractFor starters, there's no need to repeat PositionType in the WHEN lines. You've specified it in the CASE line. Secondly, if the 1 or -1 is a numeric value, they shouldn't be surrounded by quotes. Third, the "}" is wrong. Fourth, no END.|||Hi,

You can re-edit the CASE statement as
CASE PositionType
WHEN 1 THEN 'BUY'
WHEN -1 THEN 'SELL'
END AS [BS],

SELECT
CASE PositionType
WHEN 1 THEN 'BUY'
WHEN -1 THEN 'SELL'
END AS [BS],
CAST(TradePrice as float(20,8) ) AS [Price],
Quantity AS Volume,
LEFT(Contracttype,1) as KIND,
strike,
expiringdate,
comment,
SUBSTRING(contract+CONVERT(varchar,expiringdate),1 ,20) AS [FEEDCODE]
FROM TS_Positions
WHERE
(Contract LIKE 'LI%') OR
(Contract LIKE 'LK%') OR
(Contract LIKE 'LL%') OR
(Contract LIKE 'LM%')
ORDER BY Contract

Eralper
http://www.kodyaz.com|||Thanks Eralper! That worked but i decided to do it this way.

SELECT case positiontype when '1' then 'buy' when '-1' then 'sell' else 'none' end AS [B/S]...

Is there anyway where i can put on there to NOT show the NONE for the else? So i would only show the B(1) and S(-1).

Also on my query i would like to add 2 new columns at the end for example:

select col1,col2, , ‘portfolio’ as Portfolio,‘markets’ as Markets

Soo i should put that at the end of my query so it will look like this correct?

SELECT case positiontype when '1' then 'buy' when '-1' then 'sell' else 'none' end AS [B/S], comment, CAST(TradePrice as float(20,8) )AS [Price], Quantity AS Volume,
LEFT(Contracttype,1) as KIND,
strike, expiringdate, (SUBSTRING (contract+CONVERT(varchar,expiringdate),1,20)) AS [FEEDCODE], col1,col2, , ‘portfolio’ as Portfolio,‘markets’ as Markets
FROM TS_Positions
WHERE (Contract LIKE 'LI%') OR
(Contract LIKE 'LK%') OR
(Contract LIKE 'LL%') OR
(Contract LIKE 'LM%')
ORDER BY PositionType

2 new columns for my query would be Portfolio and Markets at the end..
Right now the columns without the config has:
B/S comment Price Volume Kind Strike Expiringdate Feedcode

New query would include 2 columns
B/S comment Price Volume Kind Strike Expiringdate Feedcode Portfolio Markets