Solved

Create a branch view from hierarchy tables.

Posted on 2012-04-10
3
612 Views
Last Modified: 2012-04-11
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

0
Comment
Question by:E43509
  • 2
3 Comments
 
LVL 27

Accepted Solution

by:
sujith80 earned 500 total points
ID: 37831036
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
 

Author Closing Comment

by:E43509
ID: 37832064
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
 
LVL 27

Expert Comment

by:sujith80
ID: 37832644
Thanks. Glad to know that it helped.
Let me know if you need some assistance with the query.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now