connect by in ms-sql server

[parijat67]

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

[tamilselvan]

There is a work around:
Consider this Hierarchy:

.                     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