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,154
    Thanks
    770
    Thanked 2,699 Times in 1,543 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,773
    Thanks
    1,779
    Thanked 1,331 Times in 994 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,154
    Thanks
    770
    Thanked 2,699 Times in 1,543 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
  •  
Watch Game of Thrones Online Game of Thrones S07E01 Streaming GoT Season 7 Streaming Game of Thrones Season 7 Stream game of thrones season 7 episode 1 stream got season 7 streaming game of thrones episodes download game of thrones season 7 spoilers game of thrones season 7 predictions viral viral news viral stories viral news today usa news usa news today dog meat ban yulin festival Watch Game of Thrones Online Game of Thrones S07E01 Streaming GoT Season 7 Streaming Game of Thrones Season 7 Stream game of thrones season 7 episode 1 stream got season 7 streaming game of thrones episodes download