Solved

CASE Query

Posted on 2007-03-27
3
194 Views
Last Modified: 2010-03-20
Hi i'm trying to run a CASE query, 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

Sorry i know i still have a lot to learn but thank you for helping me, i'm fairly new to SQL past month I have been working with SQL handson.
0
Comment
Question by:jcollazo81
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 35

Expert Comment

by:Raynard7
ID: 18804261
You either need to define the case after the case statement or after the when not both, so you could do

select case positiontype when '1' then 'buy' when '-1' then ......

or you could do

select case when positiontype = '1' then 'buy' when positiontype = '-1' then ....
0
 

Author Comment

by:jcollazo81
ID: 18804317
both i get this error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.



1.

SELECT   case positiontype when '1' then 'buy' when '-1' then 'sell', 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.


SELECT   case when positiontype = '1' then 'buy' when positiontype = '-1' then 'sell', 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
0
 
LVL 35

Accepted Solution

by:
Raynard7 earned 250 total points
ID: 18804333
you need to end your statements, ie

case positiontype when '1' then 'buy' when '-1' then 'sell' else 'not either' end,
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question