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 |
|
WebKill
Starting Member
32 Posts |
 Posted - 2012-09-07 : 12:47:03
|
I have a query that works perfectly fine in my test enviroment, but when I try to run it against the production database (exact same data) I get no results, in fact the only way I get results is if I change the date to our production start date of 8/27/12, and that is the only day I can get data from this particular query.The goal is to get a set of unique rulenumber lines (2 records from each rulenumber that is present in the criteria), doesn't matter if they are random, the goal is to just see any two records for each unique rule number. I had a query before that worked but took wayyy too long to run, I figured this one out and it ran fast, but when I tried in production I came up blank.SELECT * FROM (SELECT DISTINCT RuleNum FROM Data where ClientID = 'Client1') AS A CROSS APPLY ( SELECT TOP 2 * FROM Data B WHERE B.RuleNum = A.RuleNum ) AS NewTableName where ClientID = 'Client1' and Rundate >= '2012-09-03' and order by NewTableName.RuleNum |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-07 : 13:02:19
|
Assuming the syntax error is just a typo (you have an extra AND in there), there is no reason why the results should be different if the data is the same. Can you try to run this query and see if it returns any rows at all in the production environment? I suspect you will find none.select top 10 * from Data where clientId = 'Client1' and RunDate >= '20120903' |
 |
|
|
WebKill
Starting Member
32 Posts |
Posted - 2012-09-07 : 13:49:31
|
| That was a typo, sorry :)I have run that query (actually I did count(*)) and came up with over 17K results |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-07 : 14:22:50
|
| If you got 17K records when you run that query, then I don't know why the cross apply does not work. You can debug by specifying more conditions in the where clause that would limit the query to a few records. Then see what the inner query would return. Other than that, nothing obvious comes to mind, especially since it is working correctly in dev environment.Are both dev and production databases at the same compatibility level? |
|
|
|
WebKill
Starting Member
32 Posts |
Posted - 2012-09-07 : 14:42:56
|
| Yep, they are teh same, it's weird that an older day will work, but no other day in production. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-08 : 17:19:50
|
| are you sure you've records in your data table which satisfy condition Rundate >= '2012-09-03'?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Cross Apply Query Issue