Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Create a branch view from hierarchy tables.

Posted on 2012-04-10
3
Medium Priority
?
629 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 27

Accepted Solution

by:
sujith80 earned 2000 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

598 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