Geek Answers Handbook

SQL Server equivalent of Oracle "CONNECT BY PRIOR" and "ORDER SIBLINGS BY"

I have this Oracle code structure that I am trying to convert to SQL Server 2008 ( Note: I used common names, nested column names and table names in square brackets '[]', and did 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 equivalent SQL Server template for the specified code?

In particular, I am struggling with Oracle LEVEL and 'ORDER SIBLINGS BY constructs .

Note: The above "code" is the final result of a set of Oracle procedures. In principle, a WHERE clause is created dynamically and changes depending on the parameters passed. A block of code starting with "CONNECT BY PRIOR" is hardcoded.

Reference:

The CONNECT BY PRIOR of ORACLE simulation in SQL SERVER is approaching, but that doesn’t explain how to handle the LEVEL and ORDER SIBLINGS constructs .... And my mind goes into a rotation!

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

corresponds 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 original template to work with, it will go a long way in helping me build SQL Server stored processes to create the right T-SQL statement.

Help would be greatly appreciated.

+4
source share
2 answers

Simulate a LEVEL column

The level column can be easily modeled by increasing the 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;

Modeling order siblings by

Imitating order siblings by bit more complicated. Assuming we have a sort_order column that defines the order of elements per parent (and not a general sort order - because then order siblings not needed), then we can create a column that gives us a general 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 sort_path looks so complicated that SQL Server (at least the version you are using) does not have a simple function to format numbers with leading zeros. In Postgres, I would use an integer array so that conversion to varchar not necessary, but this also does not work in SQL Server.

+9
source

The option set by the user "a_horse_with_no_name" worked for me. I changed the code and applied it to the menu generator query, and it worked for 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;
0
source

More articles:


All Articles