INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Get data in a child parent relationship

Get data in a child parent relationship

(OP)
Hi all,

I currently have a table with data in this format:

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

CODE --> 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.

RE: Get data in a child parent relationship

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

RE: Get data in a child parent relationship

(OP)
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

RE: Get data in a child parent relationship

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.

RE: Get data in a child parent relationship

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.



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

RE: Get data in a child parent relationship

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

CODE --> 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,

Quote (infinitizon)

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

CODE --> 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 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close