SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cross Apply Query Issue		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WebKill
Starting Member

10 Posts
Posted - 09/07/2012 :  12:47:03  Show Profile  Reply with Quote
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
Edited by - WebKill on 09/07/2012 13:19:03

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 09/07/2012 :  13:02:19  Show Profile  Reply with Quote
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'
Go to Top of Page

WebKill
Starting Member

10 Posts
Posted - 09/07/2012 :  13:49:31  Show Profile  Reply with Quote
That was a typo, sorry :)

I have run that query (actually I did count(*)) and came up with over 17K results
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 09/07/2012 :  14:22:50  Show Profile  Reply with Quote
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?
Go to Top of Page

WebKill
Starting Member

10 Posts
Posted - 09/07/2012 :  14:42:56  Show Profile  Reply with Quote
Yep, they are teh same, it's weird that an older day will work, but no other day in production.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47069 Posts
Posted - 09/08/2012 :  17:19:50  Show Profile  Reply with Quote
are you sure you've records in your data table which satisfy condition Rundate >= '2012-09-03'?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000