Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL T-SQL Query

Posted on 2007-03-27
13
Medium Priority
?
322 Views
Last Modified: 2013-11-30
SQL Query!

Help... So i created this query for this client


SELECT  TradePrice as Price, Quantity as Volume,  Contracttype 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
__________________-

Data result was
Price              Volume        Kind               Strike             date              Comment       Life

0.0      1500      Call      97.25      20060106      NULL      LIF0620060106

2 questions--
1. How can I cut the result data for KIND just to show to C or P or F(basically the 1st letter not the whole word).
2. How can i change the result Decimal for Price column. It shows 0 but its really 15.625 or 16.25

Adding the query to above statement would be GREAT!!!!!! Thanks

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
  • 6
  • 4
  • 3
13 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18802934
SELECT  TradePrice 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
 

Author Comment

by:jcollazo81
ID: 18802959
That worked perfect... and what about Decimal point for that query? Thank you again!!!!!
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18803011
I didn't understand that part clearly, if you are saying it is returning 0 , then you need to check the table, from the query side, there is nothing to do, as it is just a select statement

Now if you wanna round it to 2 decimals , use this

SELECT CAST(TradePrice as numeric(10,2) )  as Price
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18803066
note.
WHERE     (Contract LIKE 'LI%') OR
                      (Contract LIKE 'LK%') OR
                      (Contract LIKE 'LL%') OR
                      (Contract LIKE 'LM%')


can be written:
WHERE     (Contract LIKE 'L[IKLM]%' )
0
 

Author Comment

by:jcollazo81
ID: 18803362
Sorry if you actually open up the table thru EManager it shows TradePrice data for example:

15.625
48440
but my query shows as 0 for all of them.. Weird.
I right clicked on Design table and it shows as a FLOAT for Data type... Length 8. Any ideas?

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18803540
just run the script without the where part  :)
0
 

Author Comment

by:jcollazo81
ID: 18803662
I seee ahhhhhh. But there is no  way to add where and get the same data for the PRICE Column?

I see the data there without the Where in it...........
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18805834
>I right clicked on Design table and it shows as a FLOAT for Data type... Length 8. Any ideas?
for a PRICE, float is actually a bad choice. better use numeric / decimal. float / real are floating-point stored, and hence unprecise.

back to the problem: what version / edition are you using?
could it be that all the rows that you show with the where clause actually do have 0 in the price column?
0
 

Author Comment

by:jcollazo81
ID: 18807223
I opened up the table view EM and it shows data on there. When i don't include the where on the query it shows the correct data as well.
SELECT   case positiontype when '1' then 'buy' when '-1' then 'sell' else 'not either' 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]    
FROM       TS_Positions

VS


SELECT   case positiontype when '1' then 'buy' when '-1' then 'sell' else 'not either' 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]    
FROM       TS_Positions
WHERE     (Contract LIKE 'LI%') OR
                      (Contract LIKE 'LK%') OR
                      (Contract LIKE 'LL%') OR
                      (Contract LIKE 'LM%')
ORDER BY Contract
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18807256
can you please show (upload) the result of the following queries:

select top 10 * from ts_positions

select top 10 * from ts_positions
where (Contract LIKE 'L[IKLM]%')

select top 10 * from ts_positions
where (Contract LIKE 'L[IKLM]%')
order by Contract

0
 

Author Comment

by:jcollazo81
ID: 18807354
select top 10 * from ts_positions:
                                                                                                                                Price
600628      TYG06      Put      108.0      -1      567      15.625      

select top 10 * from ts_positions
where (Contract LIKE 'L[IKLM]%')
                                                                                                                                Price
1173259      LIM07      Call      96.125      -1      1000      0.01      


select top 10 * from ts_positions
where (Contract LIKE 'L[IKLM]%')
order by Contract
                                                                                                                                Price
1211617      LIF06      Call      97.25      -1      1500      0.0      NULL      
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 18807838
which column is the price? I ask, because the number of columns seems to be different in the 2 outputs, ie the last output has 1 more column (at least what you posted...)

anyhow, this looks like all the rows with contracts like L... have prices that are empty/null ?
what data type is the field TradePrice in the table / view? (again, do NOT use float !!)

0
 

Author Comment

by:jcollazo81
ID: 18808012
well this is just a query i think the programmers created the FLoat on that column. Right clicked on the table and DEsign table..
                     Column Name  Data Type    Length       Allow Nulls
      TradePrice      float      8      1

we use Float for Strike and i am able to collect the corrcet data for that:
                     Column Name  Data Type    Length       Allow Nulls
      Strike      float      8      1

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

618 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