Get data in a child parent relationship

[infinitizon]

Hi all,

I currently have a table with data in this format:

prd_id          |          prd_dsc         |     mdl_dsc
----------------------------------------------------------
001	        |   Regular Portfolio	   |    Equities
001-CF-000001	|   Fixed Income Fund	   |     Funds
0002	        |   Managed Portfolio	   |    Equities
001-NF-000374   |	Ivory Fund	   |     Funds
B1	        |   90 Day Treasury Bills  |     Bills

What I want now is a view to get the data in a child -parent relationship thus:

id   |  p_id   |  label
----------------------------
1    |  null   |  Equities
2    |  null   |  Funds
3    |  null   |  Bills
4    |  1      |  Regular Portfolio
5    |  2      |  Fixed Income Fund
6    |  1      |  Managed Portfolio
7    |  2      |  Ivory Fund
8    |  3      |  90 Day Treasury Bills

Any ideas please...

Thanks.

 

[Andrzejek]

Looks like you want id field to be a consecutive number and a label field to be prd_dsc and mdl_dsc fields combined. But where is the p_id field’s data coming from?

Have fun.

---- Andy

 

[infinitizon]

Well, the id could be any thing (e.g the first 3 letters of the label) as long as there is a relationship between the id and the parent_id (p_id) that states that this is a child of that

 

[taupirho]

If you used the dense_rank analytic on your mdl_desc column , it would get you close to what you want.

In order to understand recursion, you must first understand recursion.

 

[SantaMufasa]

Since I'm not certain what represents parent-key values on child rows, I'll let you discern how you'd like to tailor the following syntax to fit your needs. First, I list some employee data with each row's primary key (ID) and each row's relationship to its manager (manager_id):

select id,last_name,manager_id from s_emp order by id;

ID LAST_NAME                 MANAGER_ID
-- ------------------------- ----------
 1 Velasquez
 2 Ngao                               1
 3 Nagayama                           1
 4 Quick-To-See                       1
 5 Ropeburn                           1
 6 Urguhart                           2
 7 Menchu                             2
 8 Biri                               2
 9 Catchpole                          2
10 Havel                              2
11 Magee                              3
12 Giljum                             3
13 Sedeghi                            3
14 Nguyen                             3
15 Dumas                              3
16 Maduro                             6
17 Smith                              6
18 Nozaki                             7
19 Patel                              7
20 Newman                             8
21 Markarian                          8
22 Chang                              9
23 Patel                              9
24 Dancs                             10
25 Schwartz                          10

25 rows selected.

Now, using Oracle's unique CONNECT BY...START WITH hierarchical tree-walking code, here is the employee-manager org chart for the data, above:

select lpad(' ',level*3)||LAST_NAME "Management Hierarchy"
FROM EMP
start with manager_id is null
CONNECT BY PRIOR ID = MANAGER_ID;

Management Hierarchy
---------------------
   Velasquez
      Ngao
         Urguhart
            Maduro
            Smith
         Menchu
            Nozaki
            Patel
         Biri
            Newman
            Markarian
         Catchpole
            Chang
            Patel
         Havel
            Dancs
            Schwartz
      Nagayama
         Magee
         Giljum
         Sedeghi
         Nguyen
         Dumas
      Quick-To-See
      Ropeburn

25 rows selected.

Let us know if you have additional questions about Oracle's hierarchical tree walking.





Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[infinitizon]

Alright, thanks guys. This is what I was looking for

CREATE OR REPLACE FORCE VIEW "V_WB_15_PRD_LV" ("PRD_ID", "P_ID", "PRD_DSC") AS 
  SELECT UPPER(SUBSTR(mdl_dsc,1,3)) AS prd_id, NULL p_id, mdl_dsc AS prd_dsc
  FROM (SELECT DISTINCT(mdl_dsc) AS mdl_dsc 
        FROM t_wb_15_prd) 
  UNION ALL
  SELECT prd_id, UPPER(SUBSTR(mdl_dsc,1,3)) AS p_id, prd_dsc
FROM t_wb_15_prd ;

Imagine such a simple stuff I had to take you all through. And again like I said,

the id could be any thing (e.g the first 3 letters of the label) as long as there is a relationship between the id and the parent_id (p_id) that states that this is a child of that

I eventually got this :=

PRD_ID          |        P_ID      |        PRD_DSC
--------------------------------------------------
EQU             |       Null       |         Equities 
BIL             |       Null       |         Bills
FUN             |       Null       |         Funds
001             |       EQU        |         Regular Portfolio
001-CF-000001   |       FUN        |         Fixed Income Fund
0002            |       EQU        |         Managed Portfolio
001-NF-000374   |       FUN        |         Ivory Fund
B1              |       BIL        |         90 Day Treasury Bills