Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    74

    Unanswered: Can I handle hierarchies like Oracle ?

    Hi SQL Server Experts,

    Oracle has a 'Connect by prior' extension to SQL that is neat for display hierarchies.

    Does SQL Server have anything simialr or would I use T-SQL or ???

    I will appreciate any suggestions and advice.

    B.Dimple
    Junior DBA

  2. #2
    Join Date
    Jan 2004
    Location
    Romania - Bucharest
    Posts
    50
    No. I faced a similar problem in the past.

  3. #3
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53

    Yes you can

    As long as you have the base of the hierarcy defined you can add levels to the table information and create your hierarchy.

    This is not an elegant solution but it does work.

    Assume the following Table and Field names.

    tblDepartment - table of organization departments
    departmentNumber - department number of the selected organization
    parentDepartmentNumber - department number of preceding branch
    deptLevel - hierachical level from 1

    (Only the base of the hierarchy will NOT have a parent associated.

    step 1---
    UPDATE tblDepartment
    SET tblDepartment.deptLevel = 1
    WHERE (tblDepartment.parentDepartmentNumber IS NULL)

    step 2---
    /* L2 Levels */
    UPDATE tblDepartment
    SET tblDepartment.deptLevel = 2
    FROM tblDepartment
    INNER JOIN tblDepartment as tblParent
    ON tblParent.departmentNumber = tblDepartment.parentDepartmentNumber
    WHERE tblParent.deptLevel = 1

    continue step 2 for all levels

    referencing---
    SELECT DISTINCT
    level1.departmentNumber AS departmentNumber1,
    Level2.departmentNumber AS departmentNumber2,
    Level3.departmentNumber AS departmentNumber3,
    Level4.departmentNumber AS departmentNumber4,
    Level5.departmentNumber AS departmentNumber5,
    Level6.departmentNumber AS departmentNumber6,
    Level7.departmentNumber AS departmentNumber7
    FROM tblDepartment AS level7
    RIGHT
    JOIN
    tblDepartment AS level6 ON
    level7.PARENT_departmentNumber = Level6.departmentNumber
    RIGHT JOIN
    tblDepartment AS level5 ON
    level6.PARENT_departmentNumber = Level5.departmentNumber
    RIGHT JOIN
    tblDepartment AS level4 ON
    level5.PARENT_departmentNumber = Level4.departmentNumber
    RIGHT JOIN
    tblDepartment AS level3 ON
    level4.PARENT_departmentNumber = Level3.departmentNumber
    RIGHT JOIN
    tblDepartment AS level2 ON
    level3.PARENT_departmentNumber = Level2.departmentNumber
    RIGHT JOIN
    tblDepartment AS level1 ON
    level2.PARENT_departmentNumber = Level1.departmentNumber

  4. #4
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53

    Re: Yes you can

    Appologies BUT the field

    "PARENT_departmentNumber" in the Joins should read

    "parentDepartmentNumber"

    I inadvertently copied my code incorrently

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What if you have 10 levels?

    You actually need a loop to go to n levels...

    but if you wait awhile...Yukon will have this....

    And there are many ways to denormalize this...

    Check this out:

    http://www.sqlteam.com/item.asp?ItemID=8866
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53
    Brett,

    I agree. My first comment was that it was not an elegant solution.

    I actually use the structure defined in the artice from your link to create a lineage (a DN in my case) for populating an X500 directory.

    For me it was simple because the directory has a max of 6 levels off of the root DN.

    I wish I had seen this article before I spent the time devloping the logic myself.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •