Oracle Connect by Clause Equivalent in SQL Server

SQL Server Equivalent of Oracle 'CONNECT BY PRIOR', and 'ORDER SIBLINGS BY'

Simulating the LEVEL column

The level column can easily be simulated by incrementing a counter in the recursive part:

WITH tree (empid, name, level) AS  (
  SELECT empid, name, 1 as level
  FROM emp
  WHERE name = 'Joan'

  UNION ALL

  SELECT child.empid, child.name, parent.level + 1
  FROM emp as child
    JOIN tree parent on parent.empid = child.mgrid
)
SELECT name 
FROM tree;

---

Simulating order siblings by

Simulating the order siblings by is a bit more complicated. Assuming we have a column sort_order that defines the order of elements per parent (not the overall sort order - because then order siblings wouldn't be necessary) then we can create a column which gives us an overall sort order:

WITH tree (empid, name, level, sort_path) AS  (
  SELECT empid, name, 1 as level, 
         cast('/' + right('000000' + CONVERT(varchar, sort_order), 6) as varchar(max))
  FROM emp
  WHERE name = 'Joan'

  UNION ALL

  SELECT child.empid, child.name, parent.level + 1, 
         parent.sort_path + '/' + right('000000' + CONVERT(varchar, child.sort_order), 6) 
  FROM emp as child
    JOIN tree parent on parent.empid = child.mgrid
)
SELECT * 
FROM tree
order by sort_path;

The expression for the sort_path looks so complicated because SQL Server (at least the version you are using) does not have a simple function to format a number with leading zeros. In Postgres I would use an integer array so that the conversion to varchar isn't necessary - but that doesn't work in SQL Server either.

ORACLE Connect by clause equivalent in SQL Server

The SQL Server 2005+ equivalent of Oracle's CONNECT BY hierarchical query syntax is to use a recursive CTE. SQL Server 2008 added HierarchyID. Here's an example of a recursive CTE:

WITH EmployeeHierarchy (EmployeeID, LastName, FirstName, ReportsTo, HierarchyLevel) AS (
   SELECT EmployeeID,
          LastName,
          FirstName,
          ReportsTo,
          1 as HierarchyLevel
     FROM Employees
    WHERE ReportsTo IS NULL
   UNION ALL
   -- Recursive step
   SELECT e.EmployeeID,
          e.LastName,
          e.FirstName,
          e.ReportsTo,
          eh.HierarchyLevel + 1 AS HierarchyLevel
     FROM Employees e
     JOIN EmployeeHierarchy eh ON e.ReportsTo = eh.EmployeeID)
  SELECT *
    FROM EmployeeHierarchy
ORDER BY HierarchyLevel, LastName, FirstName 

Googling "hierarchical CTE" and/or "recursive CTE" will turn up numerous results. I took the example query from the 4GuysFromRolla.com.

Recursive CTEs are now ANSI standard - the syntax wasn't supported until Oracle 11g as I understand.

SQL Server equivalent to Oracle CONNECT BY and LEVEL pseudocolumn

One way I've done it in the past is querying spt_values like this:

SELECT number
FROM master..spt_values
WHERE 
    type = 'P'
    AND number <= 255

However, it doesn't have a full list of numbers. An alternative option would be to create a Recursive CTE like such:

WITH CTE AS (
  SELECT 1 as Number
  UNION ALL
  SELECT Number+1
  FROM CTE 
  WHERE Number < 100 
)
SELECT * FROM CTE

SQL Fiddle Demo

Simulation of CONNECT BY PRIOR of Oracle in SQL Server

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.

Edit: apparently the original querant requires a specific example, here's one from the IBM site whose URL I already gave. Given a table:

CREATE TABLE emp(empid  INTEGER NOT NULL PRIMARY KEY,
                 name   VARCHAR(10),
                 salary DECIMAL(9, 2),
                 mgrid  INTEGER);

where mgrid references an employee's manager's empid, the task is, get the names of everybody who reports directly or indirectly to Joan. In Oracle, that's a simple CONNECT:

SELECT name 
  FROM emp
  START WITH name = 'Joan'
  CONNECT BY PRIOR empid = mgrid

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(empid, name) AS 
   (SELECT empid, name 
    FROM emp
    WHERE name = 'Joan'
        UNION ALL
    SELECT nplus1.empid, nplus1.name 
    FROM emp as nplus1, n
    WHERE n.empid = nplus1.mgrid)
SELECT name 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.

START WITH' equivalent expression in MS-SQL

Not directly. You can use recursive CTE (common table expressions) like the following, which requires a little bit more of writing:

;WITH RecursiveCTE AS
(
    -- Anchor (START WITH)
    SELECT
        customer_code, 
        customer_desc, 
        customer_category,
        Level = 1
    FROM
        customers_master AS C
    WHERE
        C.some_column = '0'

    UNION ALL

    -- Recursive join
    SELECT
        C.customer_code, 
        C.customer_desc, 
        C.customer_category,
        Level = R.Level + 1
    FROM
        RecursiveCTE AS R -- Note that we are referencing a table that we are just declaring as CTE
        INNER JOIN customers_master AS C ON
            R.CUSTOMER_CODE = C.PARENT_CUSTOMER_CODE
)
SELECT
    R.*
FROM
    RecursiveCTE AS R

Will need to review the recursive join's columns a little bit, but you should get the idea.

Change select with connect by prior from Oracle to SQL Server

Here is how you can do this (in Oracle, the only flavor I know) using a recursive query. "The web" reports SQL Server implements recursive queries as well, and with the same syntax (I believe all of this is SQL Standard compliant, so that's not surprising). Give it a try.

Instead of creating a table, I put all the test data in the first CTE. When you try this solution, delete the CTE named inputs first, and use your actual table name in the rest of the query.

with
     inputs ( location, parent ) as (
       select 'A001' , 'Downstream' from dual union all
       select 'A002' , 'A001'       from dual union all
       select 'A003' , 'A002'       from dual union all
       select 'A004' , 'A002'       from dual union all
       select 'A004B', 'A004'       from dual union all
       select 'B005' , 'Downstream' from dual
     ),
     r ( lvl, location ) as (
       select  1, location
         from  inputs
         where parent = 'Downstream'
       union all
       select  r.lvl + 1, i.location
         from  r join inputs i on r.location = i.parent
     )
     search depth first by lvl set ord
select lpad(' ', 2 * (lvl-1), ' ') || location as location
from   r
order by ord
;

LOCATION
--------------------
A001
  A002
    A003
    A004
      A004B
B005

6 rows selected.

ADDED: It seems SQL Server doesn't have the search depth/breadth first clause for recursive CTE's (or perhaps the syntax is different). In any case, here is a primitive "manual" implementation of the same:

with  (   .........   ),
     r ( lvl, location, ord ) as (
       select  1, location, location
         from  inputs
         where parent = 'Downstream'
       union all
       select  r.lvl + 1, i.location, r.location || '/' || i.location
         from  r join inputs i on r.location = i.parent
     )
select lpad(' ', 2 * (lvl-1), ' ') || location as location
from   r
order by ord
;

I need to convert this query from Oracle to SQL Server

SQL Server does not have CONNECT BY. You need to use a recursive CTE.

Unfortunately, you cannot put a recursive CTE into a subquery or derived table. So the easiest way to solve your particular problem is to use an inline Table Valued Function

CREATE OR ALTER FUNCTION dbo.GetMaxRecords (@fee_record_code int)
RETURNS TABLE
AS RETURN

WITH cte AS (
    SELECT fee_record_code
    FROM fees_list ffl3 
    WHERE ffl3.fee_record_code = @fee_record_code

    UNION ALL

    SELECT fee_record_code
    FROM fees_list ffl3
    JOIN cte ON cte.fee_record_code = ffl3.fes_associated_record
) 
SELECT
  fee_record_code = MAX(cte.fee_record_code)
FROM cte;

go

SELECT
    ffl2.fee_record_code,
    final_record_code = (
      SELECT r.fee_record_code
      FROM dbo.GetMaxRecords( ISNULL(ffl2.fes_associated_record, ffl2.fee_record_code) ) r
    )
FROM 
    fees_list ffl2;

---

Related Topics