-
connect by in ms-sql server
Hi,
I have the following oracle select stmt:-
--------------------------------------------------------
select tax_region_cd from ref_tax_region
start with tax_region_cd = 'VC_CITY1'
connect by prior parent_tax_region_cd = tax_region_cd
--------------------------------------------------------
How can i convert it into ms-sql server script? Do we have connect by clause in ms-sql? If not then what can be the work around?
thanking you,
Parijat Paul
-
There is a work around:
Consider this Hierarchy:
PHP Code:
. Tamil .
/ | .
/ | .
/ | .
Tom Raj .
/ \ / | | .
/ \ / | | .
Scott Bush Veera Appu Mike
SQL> desc t1
Name Null? Type
----------------- -------- ------------
NAME CHAR(10)
LFT NUMBER(38)
RGT NUMBER(38)
SQL> select * from t1 ;
NAME LFT RGT
---------- ---------- ----------
Tamil 1 16
Tom 2 7
Scott 3 4
Bush 5 6
Raj 8 15
Veera 9 10
Appu 11 12
Mike 13 14
To list employees reporting to Tom, run the query:
SQL> get x1
1 select a.name "Boss", b.name "Employee"
2 from t1 a, t1 b
3 where a.name = 'Tom'
4 and b.lft > a.lft
5* and b.lft < a.rgt
SQL> /
Boss Employee
---------- ----------
Tom Scott
Tom Bush
To list employees reporting to Tamil,
SQL> get x1
1 select a.name "Boss", b.name "Employee"
2 from t1 a, t1 b
3 where a.name = 'Tamil'
4 and b.lft > a.lft
5* and b.lft < a.rgt
SQL> /
Boss Employee
---------- ----------
Tamil Tom
Tamil Scott
Tamil Bush
Tamil Raj
Tamil Veera
Tamil Appu
Tamil Mike
7 rows selected.
The difficult part of the design is assigning left number and right number, and maintaining the hierarchy. The left and right numbers of each node contain the left and right numbers of the ancestors of that node. For assigning the left and right numbers, use a procedure/function.
Tamil
Last edited by tamilselvan; 11-05-2004 at 02:32 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|