Create a branch view from hierarchy tables.

I'm struggling with creating a view from two related tables that deal with a heirarchy.  I have to expose the data in a view in a defined manner.

The base tables are courses with the course code and id, and a linkage table that shows the hierarchical relationship between courses (parent and child) related by the course code

The output needs to be output in 4 columns:  
ID, PARENT_ID, SORT_ORDER, MYTITLE

1  0  1  XX-NONTEC-FUND  
2  1  1  XX-FIC-PROCUSE  
3  1  2  XX-FID-HU  
4  3  1  SWT-WSUP  
5  3  2  XX-FIC-HU  
6  1  3  XX-FIM-WOHOLDQ  
7  6  1  GET-PAT  
8  6  2  GET-PATCO  
9  6  3  SWT-WSUP  
10  6  4  XX-FIB-ESOMS  

The ID must be a auto incremented style column.
A parent_id of 0 means you are at the root (top level).

The sort_order always begins from 1 in each branch of the tree and goes up to 2,3,4, ...

The mytitle is the course code..

As you see by the image below, this is how the data relates to each other.

Required Output
Here is a pictorial showing the data in an indented list
Indented view
Below is sample table create and insert code.

-- List of courses
CREATE TABLE mycrs
    (cd_crs                         VARCHAR2(15 BYTE) NOT NULL,
    id_crs                         NUMBER(*,0) NOT NULL)
/

--  The cd_crs field is really the primary key for this table.

INSERT INTO mycrs 
VALUES('XX-NONTEC-FUND',21711);
INSERT INTO mycrs 
VALUES('XX-FIC-PROCUSE',21710);
INSERT INTO mycrs 
VALUES('XX-FID-HU',21709);
INSERT INTO mycrs 
VALUES('SWT-WSUP',14285);
INSERT INTO mycrs 
VALUES('XX-FIC-HU',21708);
INSERT INTO mycrs 
VALUES('XX-FIM-WOHOLDQ',27459);
INSERT INTO mycrs 
VALUES('GET-PAT',17243);
INSERT INTO mycrs 
VALUES('GET-PATCO',20533);
INSERT INTO mycrs 
VALUES('XX-FIB-ESOMS',27430);
INSERT INTO mycrs 
VALUES('XX-FIB-WOHOLD',27452);
INSERT INTO mycrs 
VALUES('XX-FIB-WOHOLDR',27460);
INSERT INTO mycrs 
VALUES('SWT-FME',20955);


-- heirarchy table relating child course to a parent course.
CREATE TABLE mylinkage
    (parent_cd_crs                  VARCHAR2(15 BYTE) NOT NULL,
    child_cd_crs                   VARCHAR2(15 BYTE) NOT NULL)

INSERT INTO mylinkage 
VALUES('XX-FID-HU','SWT-WSUP');
INSERT INTO mylinkage 
VALUES('XX-FID-HU','XX-FIC-HU');
INSERT INTO mylinkage 
VALUES('XX-FIM-WOHOLDQ','GET-PAT');
INSERT INTO mylinkage 
VALUES('XX-FIM-WOHOLDQ','GET-PATCO');
INSERT INTO mylinkage 
VALUES('XX-FIM-WOHOLDQ','SWT-WSUP');
INSERT INTO mylinkage 
VALUES('XX-FIM-WOHOLDQ','XX-FIB-ESOMS');
INSERT INTO mylinkage 
VALUES('XX-FIM-WOHOLDQ','XX-FIB-WOHOLD');
INSERT INTO mylinkage 
VALUES('XX-FIM-WOHOLDQ','XX-FIB-WOHOLDR');
INSERT INTO mylinkage 
VALUES('XX-NONTEC-FUND','SWT-FME');
INSERT INTO mylinkage 
VALUES('XX-NONTEC-FUND','XX-FIC-PROCUSE');
INSERT INTO mylinkage 
VALUES('XX-NONTEC-FUND','XX-FID-HU');
INSERT INTO mylinkage 
VALUES('XX-NONTEC-FUND','XX-FIM-WOHOLDQ');

Open in new window

E43509Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SujithConnect With a Mentor Data ArchitectCommented:
SQL> SELECT *
  2    FROM mycrs;

CD_CRS              ID_CRS
--------------- ----------
XX-NONTEC-FUND       21711
XX-FIC-PROCUSE       21710
XX-FID-HU            21709
SWT-WSUP             14285
XX-FIC-HU            21708
XX-FIM-WOHOLDQ       27459
GET-PAT              17243
GET-PATCO            20533
XX-FIB-ESOMS         27430
XX-FIB-WOHOLD        27452
XX-FIB-WOHOLDR       27460

11 rows selected.

Elapsed: 00:00:00.59
SQL>
SQL> SELECT *
  2    FROM mylinkage;

PARENT_CD_CRS   CHILD_CD_CRS
--------------- ---------------
XX-FID-HU       SWT-WSUP
XX-FID-HU       XX-FIC-HU
XX-FIM-WOHOLDQ  GET-PAT
XX-FIM-WOHOLDQ  GET-PATCO
XX-FIM-WOHOLDQ  SWT-WSUP
XX-FIM-WOHOLDQ  XX-FIB-ESOMS
XX-FIM-WOHOLDQ  XX-FIB-WOHOLD
XX-FIM-WOHOLDQ  XX-FIB-WOHOLDR
XX-NONTEC-FUND  XX-FIC-PROCUSE
XX-NONTEC-FUND  XX-FID-HU
XX-NONTEC-FUND  XX-FIM-WOHOLDQ

11 rows selected.

Elapsed: 00:00:00.57
SQL>
SQL> SELECT   y.rn ID, NVL (mc.rn, 0) parent_id,
  2           ROW_NUMBER () OVER (PARTITION BY NVL (mc.rn, 0) ORDER BY y.rn)
  3                                                                     sort_order,
  4           y.cd_crs
  5      FROM (SELECT     cd_crs, parent_cd_crs, ROWNUM rn
  6                  FROM (SELECT mc.cd_crs, ml.parent_cd_crs
  7                          FROM mycrs mc LEFT OUTER JOIN mylinkage ml
  8                               ON mc.cd_crs = ml.child_cd_crs
  9                               ) x
 10            START WITH parent_cd_crs IS NULL
 11            CONNECT BY PRIOR cd_crs = parent_cd_crs) y
 12           LEFT OUTER JOIN
 13           (SELECT     cd_crs, parent_cd_crs, ROWNUM rn
 14                  FROM (SELECT mc.cd_crs, ml.parent_cd_crs
 15                          FROM mycrs mc LEFT OUTER JOIN mylinkage ml
 16                               ON mc.cd_crs = ml.child_cd_crs
 17                               ) x
 18            START WITH parent_cd_crs IS NULL
 19            CONNECT BY PRIOR cd_crs = parent_cd_crs) mc
 20           ON y.parent_cd_crs = mc.cd_crs
 21  ORDER BY y.rn;

        ID  PARENT_ID SORT_ORDER CD_CRS
---------- ---------- ---------- ---------------
         1          0          1 XX-NONTEC-FUND
         2          1          1 XX-FIC-PROCUSE
         3          1          2 XX-FID-HU
         4          3          1 SWT-WSUP
         5          3          2 XX-FIC-HU
         6          1          3 XX-FIM-WOHOLDQ
         7          6          1 GET-PAT
         8          6          2 GET-PATCO
         9          6          3 SWT-WSUP
        10          6          4 XX-FIB-ESOMS
        11          6          5 XX-FIB-WOHOLD
        12          6          6 XX-FIB-WOHOLDR

12 rows selected.

Elapsed: 00:00:00.60

Open in new window

See the code snip
0
 
E43509Author Commented:
Just WOW !
Thanks so much.  I'm impressed.  I would not have come up with this solution as I kept playing with the start with and connect by and could not noodle it out.
0
 
SujithData ArchitectCommented:
Thanks. Glad to know that it helped.
Let me know if you need some assistance with the query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.