December 1, 2008 at 3:39 am
Hi,
I want to convert a Oracle query into sql server 2005.
Pls. help me.
Query is as below:
update FM_ProfileGroup Set Root_GroupId=V_Fnd_Root_GrpId
where GroupId in ( SELECT g.groupid GroupId
FROM fm_profilegroup g
START WITH parent_groupid=P_GroupId CONNECT BY PRIOR groupid=parent_groupid);
I want to do the updation heirarchically.
Regards
NaaG
December 1, 2008 at 4:21 am
Searc for Common Table Expressions in google
Failing to plan is Planning to fail
December 2, 2008 at 7:39 am
Having 10+ years of Oracle experience, I understand your problem.
SQL Server doesn't offer the same hierarchical functionality as Oracle's "CONNECT BY" clause.
The solutions are:
1. Use a recursive Common Table Expression (CTE). Examples in BOL and/or this forum.
2. Write a user-defined function (UDF) to traverse up or down in the hierarchy. Again, there are examples in this forum as well as links to other documents.
The choice depends upon the specific details of your hierarchy, the application, etc. as a.) there are limitations to the recursive CTE method (as I've noted in the forum) and b.) there is potential for a performance hit using a UDF vs. a recursive CTE. However, a UDF can overcome some of the limitations of recursive CTEs.
In my case, I use a UDF.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply