Re: Need help with monstrous query migrated from sql server

You've seen a classic case where a SQL statement which runs on one RDBMS does not work well on another. This does not mean that one is better than the other, just different so you'll need to look at the differences. One thing that jumps out at me is you use the classic SQL Server querying:

WHERE NOT EXISTS (SELECT 1 ....) This does not work as well in Oracle (typically) as it does in SQL Server. In Oracle, look at using a Join to filter out unwanted rows of data. And in some cases, NOT IN works better than NOT EXISTS.

In order to effectively migrate an application from one RDBMS to another, the person migrating the code should be fluent in both RDBMS versions. This can save quite a bit of time. Others have found out how things differ between the two and presented their results on the web. Do a Google search for "not exists sql server oracle" and you will find many results which show how one thing is done in Oracle and how to convert it to SQL Server. From this, you can easily do the reverse. Additionally, Books Online has similar information. And you can find information from Oracle's perspective on Technet (http://technet.oracle.com).

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown