Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Get data in a child parent relationship

Status
Not open for further replies.
Jul 28, 2011
167
NG
Hi all,

I currently have a table with data in this format:
SQL:
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:
SQL:
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.
 
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
 
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
 
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.
 
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):

Code:
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:

Code:
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.





[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.
 
Alright, thanks guys. This is what I was looking for
SQL:
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,
infinitizon 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 :=
SQL:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top