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

Question

I've got this Oracle code structure I'm trying to convert to SQL Server 2008 (Note: I have used generic names, enclosed column names and table names within square brackets '[]', and done some formatting to make the code more readable):

SELECT [col#1], [col#2], [col#3], ..., [col#n], [LEVEL] 
FROM (SELECT [col#1], [col#2], [col#3], ..., [col#n] 
      FROM [TABLE_1] 
      WHERE ... ) 
CONNECT BY PRIOR [col#1] = [col#2] 
START WITH [col#2] IS NULL 
ORDER SIBLINGS BY [col#3]

What is the SQL Server equivalent template of the above code?

Specifically, I'm struggling with the LEVEL, and 'ORDER SIBLINGS BY' Oracle constructs.

Note: The above "code" is the final output from a set of Oracle procedures. Basically, the 'WHERE' clause is built up dynamically and changes depending on various parameters passed. The code block starting with 'CONNECT BY PRIOR' is hard-coded.

---

For Reference:

The Simulation of CONNECT BY PRIOR of ORACLE in SQL SERVER article comes close, but it does not explain how to handle the 'LEVEL' and the 'ORDER SIBLINGS' constructs. ... And my mind is getting in a twist!

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

equates to:

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

---

If I have an initial template to work from, it will go a long way to helping me construct SQL Server stored procs to build up a correct T-SQL statement.

Assistance will be much appreciated.

Cleasta

The equivalent is a recursive common table expression: stackoverflow.com/questions/tagged/sql-server+recursive-query – Canadianism

This is an excerpt directly from Oracle stored proc: vSQL := 'SELECT FLEX_VALUE, DESCRIPTION, ADMIN_ENABLED, PARENT_FLEX_VALUE, DISPLAY_DESC, LEVEL FROM ('|| 'SELECT FLEX_VALUE, DESCRIPTION, ADMIN_ENABLED, PARENT_FLEX_VALUE, '||vDescField||' AS DISPLAY_DESC '|| 'FROM GL_SEGMENT'||TO_CHAR(inSEGMENT)||' '|| 'WHERE PERIOD_YEAR = '||inPERIOD_YEAR||' '; ...... Note the 'WHERE PERIOD_YEAR' ...... – Cleasta

But syntax is the least of my worries. What I need is a T-SQL equivalent for the oracle structure. – Cleasta

possible duplicate of Simulation of CONNECT BY PRIOR of ORACLE in SQL SERVER – Becoming

For the record, I've used [..] to help me identify between column names and keywords. I've also added line breaks and tabs, etc. I'm not a SQL expert, and the raw sql is unformatted! – Cleasta

Simulation of CONNECT BY PRIOR of ORACLE in SQL SERVER is almost but not quite. It doesn't explain to me how to handle LEVEL or ORDER SIBLINGS. – Cleasta

Answer 1

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.

Canadianism

OK. Thanks, I like that. That's 'LEVEL' sorted. :) Any idea how to handle ORDER SIBLINGS? – Cleasta

Yes! This looks like it might work. Assuming that sort_order column is already just a text string something like 'R301 - Area Management' or 'R402 - Casual Staff' - then it should just work ... I hope. The original Oracle code seems to only sort by the 'child'. It displays the 'parent_code' separately for every line in the results. ... I'm trying this now. :) – Cleasta

The main problem in this solution that doesn't recognize infinite loop, any hint? – Epiblast

Answer 2

The option given by the user "a_horse_with_no_name" worked for me. I changed the code and applied it to a menu generator query and it worked the first time. Here is the code:

WITH tree(option_id,
       option_description,
      option_url,
      option_icon,
      option_level,
      sort_path)
     AS (
     SELECT ppo.option_id,
            ppo.option_description,
          ppo.option_url,
          ppo.option_icon,
          1 AS option_level,
          CAST('/' + RIGHT('00' + CONVERT(VARCHAR, ppo.option_index), 6) AS VARCHAR(MAX))
     FROM security.options_table_name ppo
     WHERE ppo.option_parent_id IS NULL
     UNION ALL
     SELECT co.option_id,
            co.option_description,
          co.option_url,
          co.option_icon,
          po.option_level + 1,
          po.sort_path + '/' + RIGHT('00' + CONVERT(VARCHAR, co.option_index), 6)
     FROM security.options_table_name co,
          tree AS po
     WHERE po.option_id = co.option_parent_id)
     SELECT *
     FROM tree
    ORDER BY sort_path;

Indices

Answer 3

to get dates for last 10 days:

SELECT DISTINCT RecordDate = DATEADD(DAY,-number,CAST(GETDATE() AS DATE)) 
FROM master..[spt_values] 
WHERE number BETWEEN 1 AND 10

Thermoscope