Results 1 to 3 of 3

Thread: Help with hierarchical query output (PL/SQL)

  1. #1
    🙈 🙉 🙊 heavywater's Avatar
    Join Date
    Feb 2010
    Posts
    7,130
    Thanks
    768
    Thanked 2,680 Times in 1,530 Posts
    Rep Power
    9

    Default Help with hierarchical query output (PL/SQL)

    So I've got a large hierarchical permissions table with about 2000 rows across 5 levels. I've written something to get the structure out of this, but I'd like to clean it up a little.

    The structure table (PERM_CLASSES) has columns CLASS_ID (PK), PARENT_CLASS_ID, DESCRIPTION

    The user table (PERM_USERS) has columns CLASS_ID and ROLE

    A cut down version of the code used for the hierarchy:

    Code:
    SELECT description, role, SYS_CONNECT_BY_PATH(description, '/') path, LEVEL, pc.class_id, parent_class_id
    FROM perm_classes pc
    LEFT JOIN perm_roles pr
    ON pc.class_id=pr.class_id
    CONNECT BY PRIOR pc.class_id=parent_class_id
    START WITH parent_class_id IS NULL
    ORDER by path, role
    And a quick fiddle with some example data: http://sqlfiddle.com/#!4/dbb7e/1
    (there are normally permission under all levels of the tree, not just children, I was just lazy)

    What I want is for, in cases where the class has a matching permissions role, to only display the top level class once. So for example:

    DESCRIPTION ROLE PATH
    GrandParent class 1 /GrandParent class 1
    Parent class 1 /GrandParent class 1/Parent class 1
    Child class 1 Admin /GrandParent class 1/Parent class 1/Child class 1
    Child class 1 Read-Only Admin /GrandParent class 1/Parent class 1/Child class 1
    Child class 1 SuperUser /GrandParent class 1/Parent class 1/Child class 1
    Child class 2 Admin /GrandParent class 1/Parent class 1/Child class 2
    Child class 2 SuperUser /GrandParent class 1/Parent class 1/Child class 2
    Parent class 2 /GrandParent class 1/Parent class 2
    Child class 3 SuperUser /GrandParent class 1/Parent class 2/Child class 3

    would get tidied up to:

    DESCRIPTION ROLE PATH
    GrandParent class 1 /GrandParent class 1
    Parent class 1 /GrandParent class 1/Parent class 1
    Child class 1 Admin /GrandParent class 1/Parent class 1/Child class 1
    | Read-Only Admin /GrandParent class 1/Parent class 1/Child class 1
    | SuperUser /GrandParent class 1/Parent class 1/Child class 1
    Child class 2 Admin /GrandParent class 1/Parent class 1/Child class 2
    | SuperUser /GrandParent class 1/Parent class 1/Child class 2
    Parent class 2 /GrandParent class 1/Parent class 2
    Child class 3 SuperUser /GrandParent class 1/Parent class 2/Child class 3

    Any thoughts? I'm also going to indent description by level with lpad, but it doesn't work in the fiddle for some reason.
    Last edited by heavywater; 24-03-17 at 22:37.

  2. #2
    🍄 🍄 🍄 🍄 🍄 🍄 🍄 🍄 cold fusion's Avatar
    Join Date
    Aug 2011
    Location
    In the Mushroom Kingdom with Princess Peach
    Posts
    8,609
    Thanks
    1,735
    Thanked 1,293 Times in 968 Posts
    Rep Power
    5

    Default

    Since it's a small data set you're dealing with and it's PL/SQL (which supports imperative flow control), i'd just take the lazy route and wrap you're SQL inside a recursive loop.

  3. The Following User Says Thank You to cold fusion For This Useful Post:


  4. #3
    🙈 🙉 🙊 heavywater's Avatar
    Join Date
    Feb 2010
    Posts
    7,130
    Thanks
    768
    Thanked 2,680 Times in 1,530 Posts
    Rep Power
    9

    Default

    Not a bad suggestion... I gave up on that one and went for the even lazier route: manipulating the output in excel.

    Any thoughts on a good way to identify roles in child nodes that don't exist in all parent nodes?

Posting Permissions

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