Results 1 to 4 of 4

Thread: CASE Query

  1. #1
    Join Date
    Jun 2005
    Posts
    18

    Question Unanswered: 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 Contract

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    For 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.
    Paul

  3. #3
    Join Date
    Jan 2005
    Posts
    28
    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

  4. #4
    Join Date
    Jun 2005
    Posts
    18
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •