Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ggiree
Starting Member
3 Posts |
 Posted - 2007-09-20 : 10:12:21
|
| Hi Friends,We are facing an inconsistency in the number of records returned by the following query.SELECT Load_ID, Pair, MaturityDate, PutOrCall, Strike, SUM((CASE WHEN [DealType] = 'B' THEN 1 ELSE -1 END) * [Notional] ) AS [Not]FROM tbuOptionDealsGROUP BY Load_ID, Pair, MaturityDate, PutOrCall, Strike, TypeOfEventHAVING (SUM(CASE WHEN [DealType] = 'B' THEN 1 ELSE -1 END* [Notional]) <>0 AND ((TypeOfEvent = 'N' OR TypeOfEvent IS NULL)))order BY Load_ID, Pair, MaturityDate, PutOrCall, Strikehere Notional & strike are of type float.We migrated this data from MS Access to SQL server 2005.We are getting a count of 7741 records in access, but in Sql server 2005, each time when we execute the query we are getting different count of records (something between 7735 & 7759).Please help........thanks in advance.Gireesh G |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-20 : 19:27:04
|
| Sounds like something is updating the table constantly, so your query is giving a different result each time you run it.I presume SQL Server is moving AND ((TypeOfEvent = 'N' OR TypeOfEvent IS NULL)))from the HAVING to the WHERE, which may cause a different behaviour to Access?Kristen |
 |
|
|
ggiree
Starting Member
3 Posts |
Posted - 2007-09-21 : 06:19:16
|
| the data is not getting updated.it is static data.same issue we are facing after changing to where clause also.thanks for ur suggestion.Gireesh |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-21 : 07:13:05
|
| Just to make sure I've understood pls:You are the only user connected to the database, you have exclusive access.Each time you run this query you get a different answer?Kristen |
|
|
|
ggiree
Starting Member
3 Posts |
Posted - 2007-09-21 : 08:45:47
|
| Yes u r right.Gireesh |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-09-21 : 09:10:07
|
| The exact same query returns different results each time, or different variations of the query that you are trying?In query analyzer, copy your SELECT, just below it, so that you have the exact same sql statement repeated twice in a query analyzer window. Also, be sure you have "results to text" set. Then run it your script which should execute the exact same select twice in a row and demonstrate to us the different results are returned each time. Please post your sql script and also the results here so we can see.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-24 : 03:41:32
|
Or better, post the table data and layout, and we can try to reproduce your error?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
|
|
Inconsistency in count