Migrating SQL Query from Oracle to SQL Server 2008 or Sybase

Oracle and Microsoft SQL Server are very different than each other and if
you are migrating SQL queries or database, tables from Oracle 11g database to
Microsoft 2008 SQL server than you are bound to face some issues. Main reason
of these porting issues are features, which are supported and exists in Oracle
database, but not available in Microsoft SQL Server 2008 like SEQUENCE, Order
by clause in sub queries and derived
tables, derived table without name etc. I am sure there are few more and it
will surface based upon different database objects you are using in your tables
and queries. On other hand SQL Engine for SQL Server and Sybase are very much
similar, at least syntactically, and if you are migrating queries from SQL
Server to Sybase you can do that without
much hassle, of course there will be slight changes but not as much like Oracle.
So if you are migrating from Oracle to Sybase or SQL Server its most likely
same job and you should first start with either SQL Server or Sybase ASE and
than later migrate them from each other. In this Oracle and SQL Server tutorial
we will see couple of examples, where Oracle and SQL Server are different and
how to change those SQL queries so that it can run on Microsoft SQL Server. By
the way I have also written couple of post on queries like 10 ways to use SQL SELECT queries
and Don’t delete, truncate it. If
you like reading more on SQL queries than those are for you.

Migrating SQL queries from Oracle to SQL
Server

As I said, we faced three main problems while migrating our sql queries
from Oracle 11g to SQL Server 2008, those are related to SEQUENCE object,
order by clause in subqueries and using anonymous derived tables. Let’s see
them one by one.

1) Order
by in sub query or derived table :

Oracle supports order by clause in sub query and derived tables, but when
you try to run the same query, which is working fine in Oracle, in SQL Server
or Sybase, you will get following error because they don’t support order by
clause on derived table or sub query.

Error : The ORDER BY clause is invalid in views, inline functions,
derived tables, sub queries, and common table expressions, unless TOP or FOR
XML is also specified.

Solution: In order to solve this problem you need to remove order by
clause from sub query and derived table and move those to main result set, most
of the time you can do that but if you cant than you probably need to rewrite
whole query.

Below query will work in Oracle but will not work in Microsoft SQL
Server, as it is using order by clause in sub query:

select CUSTOMER_ID,CUSTOMER_ADDRESS from CUSTOMER where CUSTOMER_IDS IN
(select SELLER_ID from TRANSACTIONS where ITME_TYPE='PC' order by PURCHASE_DATE)

In order to make it work we need to remove order by clause from subquery
and like below:

select CUSTOMER_ID,CUSTOMER_ADDRESS from CUSTOMER where CUSTOMER_IDS IN
(select SELLER_ID from TRANSACTIONS where ITME_TYPE='PC')

if this is not what you desire than you need to rewrite this query using SQL Joins

2) Derived
table without name :

Second difference I found between Oracle and SQL Server query is that ,
oracle allow you to query derived table without specifying name but SQL
Server doesn't allow. Look at following query, which works perfectly in Oracle
but give syntax error in SQL Server :

 select count(*) from (

    select BUYER_ID from
TRANSACTIONS where SELLER_ID= 'james_2012' and item_type='PC'

    UNION

    select SELLER_ID from
TRANSACTIONS where BUYER_ID= 'james_2012' and item_type='PC'

    )

Error: Incorrect syntax near ')'

Solution: This error was extremely easy to fix, we just need to provide a
name to our derived table using "as" keyword,
here is the modified SQL query which will work in Sybase and Microsoft SQL
Server 2005, 2008 and may be on other version :

 select count(*) from (

    select BUYER_ID from
TRANSACTIONS where SELLER_ID= 'james_2012' and item_type='PC'

    UNION

    select SELLER_ID from
TRANSACTIONS where BUYER_ID= 'james_2012' and item_type='PC'

 ) as sells

3) SEQUENCE
Object:

Another problem we faced while migrating our queries form Oracle to SQL
Server is related to SEQUENCE object. We were using Oracle SEQUENCE object to
generate automatic incremented id and SEQUENCE is not
supported in Microsoft SQL Server 2008 (let me know if it does because I tried
to create SEQUENCE and it gives error "Unknown object type
'SEQUENCE' used in a CREATE, DROP, or ALTER statement"). Only
solution was to remove the SEQUENCE code and instead use IDENTITY feature of SQL Server,
which is similar to sequence and creates automatic incremented column. You
provide IDENTITY a seed and an incremental value, default is (1,1) and it
automatically generates numbers for your.

What comes to us as  pleasant
surprise was pagination queries, we had some oracle pagination queries in our
application which is written using row_num() function
and we are thinking about how to migrate them into SQL server, before
discovering that SQL Server also supports ROW_NUM() function.
In short Oracle pagination queries runs fine on SQL Server except few changes mentioned
above e.g. order by clause inside subquery or derived table.

That's it on migrating SQL queries from Oracle to SQL Server or Sybase
ASE database. I am sure there are many more difference between Oracle and
SQL Server, which I haven't covered simply because I haven't faced them. By the
way these were some of the most common changes, you need to make to run your
oracle query into SQL server. If you have also done the same job then please
let us know what kind of issue you have faced on SQL Server side and how did
you solved it. Hmm, sounds like good question?

Source:http://javarevisited.blogspot.com/2013/05/migrating-sql-query-from-oracle-to-sql-server-2008.html