?
Solved

SQL T-SQL Query

Posted on 2007-03-27
13
Medium Priority
?
321 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

770 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