Hierarchy - How to Convert Oracle Connect BY into T-SQL
-
I have a table with two columns that I received by UNION. Steps 1 and 28 are roots.
from_step_id to_step_id
0 1
0 28
1 4
4 8
8 12
12 16
28 33
33 39
39 44
44 49
In Oracle I combined these two columns in one using CONNECT BY, how can I make it in SQL Server:
step_id row_number
1 1
4 2
8 3
12 4
16 5
28 6
33 7
39 8
44 9
49 10
Thank you!!
-
You'll have to use CTE
--Ramesh
-
Thank you for reply Ramesh. I will read regarding Common Table Expressions.
-
yep, a cte may be what you're looking for.
maybe even the (free) "Microsoft SQL Server Migration Assistant for Oracle" may help you out :w00t:
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thank you ALZDBA for important info, but I am afraid I can't use this Assistant. I need to solve this problem by creating views in SQL Server.
-
May be you can try the below query....I hope it works :D:D
WITH StepCTE
AS
(
SELECT from_step_id, to_step_id, 0 as rlevel FROM yourtable WHERE from_step_id = 0
UNION ALL
SELECT CTE.to_step_id, T.to_step_id, CTE.rlevel + 1 FROM yourtable T INNER JOIN StepCTE CTE ON T.from_step_id = CTE.to_step_id
)
SELECT ROW_NUMBER() OVER( ORDER BY from_step_id ) as RowNumber, * FROM StepCTE
OPTION( MAXRECURSION 10 )
--Ramesh
-
Thanks Ramesh for your time and sending the query! I appreciate it very much. I am struggling with CTE... May I explain once again what I need to do?
I have to recreate manually views from Oracle to SQL Server. In Oracle, I have the next query:
SELECT all_steps.to_step_id step_id , rownum step_order
FROM
(
SElECT 0 from_step_id, steps.step_id to_step_id
FROM steps
WHERE steps.is_root = 1
UNION
SElECT step_transitions.from_step_id, step_transitions.to_step_id
FROM step_transitions
) all_steps
CONNECT BY all_steps.from_step_id = PRIOR all_steps.to_step_id
START WITH all_steps.from_step_id = 0;
It gives the next results:
step_id step_order
1 1
4 2
8 3
12 4
16 5
28 6
33 7
39 8
44 9
49 10
55 11
60 12
66 13
71 14
76 15
I need to receive the same results in SQL Server. I took row_number syntax from your query. I made it as:
select row_number() over (order by to_step_id asc) as row_number, all_steps.to_step_id as step_id
from (
SElECT 0 from_step_id, steps.step_id as to_step_id
FROM steps
WHERE steps.is_root = 1
UNION
SElECT step_transitions.from_step_id, step_transitions.to_step_id
FROM step_transitions) as all_steps
;
But as you can see it is ordering by to_step_id and it is not right. How to join with your StepCTE?
Thanks!
-
Olga,
You should follow the syntax "EXACTLY" as was posted above if you need a different ORDER BY just supply it at the end of the CTE.
EX:
create table steps (from_step_id int, to_step_id int )
insert into steps (from_step_id, to_step_id ) values (0, 1)
insert into steps (from_step_id, to_step_id ) values (0, 28)
insert into steps (from_step_id, to_step_id ) values (1, 4)
insert into steps (from_step_id, to_step_id ) values (4, 8)
insert into steps (from_step_id, to_step_id ) values (8, 12)
insert into steps (from_step_id, to_step_id ) values (12, 16)
insert into steps (from_step_id, to_step_id ) values (28, 33)
insert into steps (from_step_id, to_step_id ) values (33, 39)
insert into steps (from_step_id, to_step_id ) values (39, 44)
insert into steps (from_step_id, to_step_id ) values (44, 49)
WITH StepCTE
AS
(
SELECT from_step_id, to_step_id, 0 as rlevel FROM steps WHERE from_step_id = 1
UNION ALL
SELECT CTE.to_step_id, T.to_step_id, CTE.rlevel + 1 FROM steps T INNER JOIN StepCTE CTE ON T.from_step_id = CTE.to_step_id
)
SELECT from_step_id, ROW_NUMBER() OVER( ORDER BY from_step_id ) as RowNumber
FROM StepCTE
ORDER BY from_step_id ---- <<< Use the "order by" you need
GOOD LUCK
* Noel -
Thank you noeld for your reply!!
I've made it yesterday and tested. It works well.
WITH Steps_CTE
AS
(
SELECT from_step_id, to_step_id, 1 as rlevel
FROM all_steps_vw
WHERE from_step_id = 0
UNION ALL
SELECT CTE.to_step_id,
T.to_step_id,
CTE.rlevel + 1
FROM all_steps_vw T
INNER JOIN
Steps_CTE CTE
ON T.from_step_id = CTE.to_step_id
)
SELECT PROGRAMS.PROGRAM_ID, --statements continue
-- determine the step order
LEFT OUTER JOIN (
SELECT ROW_NUMBER()
OVER( ORDER BY to_step_id )
as step_order, to_step_id as step_id FROM Steps_CTE
) step_order
ON step_order.step_id = steps.step_id;
Now, how can I reference this CTE (if it is not stored in SQL Server metadata) in Crystal Reports as a source, as I did it in Oracle. Can CTE be referenced withing views? Can somebody help me ?
Thank you!
-
Just Wrap the cte in a CREATE VIEW satatement and from Crystal just use the view.
hope is clear.
* Noel -
Thanks noeld,
I have added as another topic - CTE, view and Crystal.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply