Solved

SQL T-SQL Query

Posted on 2007-03-27
13
311 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
  • 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 142

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 142

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 142

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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

822 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