Hierarchical Query in Oracle SQL

Question

I have data like this:

Data Table

From the above table I am trying to write a SQL using connect by clause to get a hierarchy like this

      MAINCONTENT

       SPECIAL
        RIDGE
         SALESCONTENT

       ANOTHERONE
        RODGE
         SOMETHING ELSE
          ANOTHER
        ...

So far this SQL below is showing my only all the children of 'MAINCONTENT' but i want to do it without passing the parameter. Also this below one is not showing me the Children of Children, meaning its not doing recursive.

    select DISTINCT parent from  MYTABLE
    connect by prior CHILD = PARENT
    start with PARENT = 'MAINCONTENT';

mikehillyer.com/articles/managing-hierarchical-data-in-mysql — Barmar
– Barmar, Commented Aug 7, 2014 at 20:42
@Barmar The link is for MySQL, it doesn't really show how to use CONNECT BY — Lamak
– Lamak, Commented Aug 7, 2014 at 20:45
Oops, sorry. I mistakenly remembered it as being more generic. — Barmar
– Barmar, Commented Aug 7, 2014 at 20:47
Anyway, you should show what you tried, so people can help you fix it. SO is not for getting other people to write your code for you. — Barmar
– Barmar, Commented Aug 7, 2014 at 20:48

Kombajn zbożowy · Accepted Answer · 2014-08-07 21:16:48Z

1

You seem to be showing only part of the data in the spreadsheet, so I'm not sure if the below is 100% correct, but:

First get rid of indirect links (direct ones should cover the entire tree) and create extra entries for top-level records. Then apply hierarchical clause.

Try the below:

WITH mytable_normalized AS (
  SELECT parent, child
    FROM mytable
   WHERE direct_link = 'Y'
   UNION ALL
  SELECT null, parent
    FROM mytable
   MINUS
  SELECT null, child
    FROM mytable
)
SELECT lpad(' ', level*2) || child
  FROM mytable_normalized
CONNECT BY prior child = parent
 START WITH parent IS NULL;

answered Aug 7, 2014 at 21:16

Kombajn zbożowy

10.8k5 gold badges33 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Hierarchical Query in Oracle SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related