CODEWITHSUNDEEP
sqlsql-serverplsql
canada canada
canada canada

Reputation: 181

Migrate from Oracle to SQL Server

I have the following query written with Oracle 

delete from EQUI_HIERARCHY; 

select max(level) into v_max_depth 
from EQUIP_D

connect by prior EQUIP_NO=PARENT_EQUIP_NO 
start with PARENT_EQUIP_NO is null;

How to transform it into SQL Server ?

Upvotes: 0

Views: 38

Answers (1)

Mahesh
Mahesh

Reputation: 8892

The SQL standard way to implement recursive queries, as implemented e.g. by IBM DB2 and SQL Server, is the WITH clause. See this article for one example of translating a CONNECT BY into a WITH (technically a recursive CTE) -- the example is for DB2 but I believe it will work on SQL Server as well.

In SQL Server, IBM DB2, or PostgreSQL 8.4 (as well as in the SQL standard, for what that's worth;-), the perfectly equivalent solution is instead a recursive query (more complex syntax, but, actually, even more power and flexibility):

    WITH n(v_max_depth ) AS 
         (SELECT max(level)
          FROM EQUIP_D
          WHERE PARENT_EQUIP_NO IS NULL
               UNION ALL
          SELECT max(level)
          FROM EQUIP_D as nplus1, n
          WHERE n.EQUIP_NO= nplus1.PARENT_EQUIP_NO)

   SELECT max(v_max_depth) FROM n

Oracle's START WITH clause becomes the first nested SELECT, the base case of the recursion, to be UNIONed with the recursive part which is just another SELECT.

SQL Server's specific flavor of WITH is of course documented on MSDN, which also gives guidelines and limitations for using this keyword, as well as several examples.

Upvotes: 1

Related Questions