Thanks a lot Lowell. CTE works perfect. This sql is run within a loop in a program and it is causing performance issue.. I have created a topic(Remote Query to Oracle very slow) in this forum regarding this problem.. Can you please tell me would using CTE increase performance in this case? Also, can we use variables in CTE?
Thank you so much for your help
can't say for sure; your other post doesn't show the loop you mentioned here; Can you post the offending code? if it's using a loop or cursor to go to Oracle linked server once per iteration, the loop needs to be replaced with a set based operation instead;
so to help you'll need to provide more info;
pasting the program code might give us insight into replacing THAT with something all done at the SQL server in one shot.
Programmers tend to think of data as one row at a time, because they can step through the code, look at the variables and values in debug, and confirm to themselves it's working the way they want;
stepping back from the picutre, and abstracting out to say "i want to do this
to the SET of data when this other criteria is true
is the DBA's job...look at things as Sets.
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!