connect by in ms-sql server
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: connect by in ms-sql server

  1. #1
    Join Date
    Dec 2001
    Posts
    120

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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)


    SQLselect 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 Tomrun the query:
    SQLget x1
      1  select a
    .name "Boss"b.name "Employee"
      
    2  from t1 at1 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
    SQLget x1
      1  select a
    .name "Boss"b.name "Employee"
      
    2  from t1 at1 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
  •  


Click Here to Expand Forum to Full Width