Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Hierarchy Query with join.

mlov83Oct 28 2016 — edited Oct 28 2016

I have a table with user id's and another table with hierarchy elements.  The table with the user id has a unique key to the table that has the hiearchy elements. What I'm trying to accomplish is to display every element of the heirachy table by user. The problem is that I have hierachy nodes that I need to traverse and display. Below is an example of the tables that I'm using and the data that I'm using.

create table fsecure(

userid varchar(100),

f_element varchar(6)

);

REM INSERTING into FSECURE

SET DEFINE OFF;

Insert into FSECURE (USERID,F_ELEMENT) values ('FFA','140');

Insert into FSECURE (USERID,F_ELEMENT) values ('FFA','280');

Insert into FSECURE (USERID,F_ELEMENT) values ('GGA','202118');

Insert into FSECURE (USERID,F_ELEMENT) values ('GGA','140');

Insert into FSECURE (USERID,F_ELEMENT) values ('RFA','202116');

  CREATE TABLE "FVAL"

   ( "FUND" VARCHAR2(6 ),

     "FUND_PRED" VARCHAR2(6)

   )

REM INSERTING into FVAL

SET DEFINE OFF;

Insert into FVAL (FUND,FUND_PRED) values ('140',null);

Insert into FVAL (FUND,FUND_PRED) values ('280',null);

Insert into FVAL (FUND,FUND_PRED) values ('2000','140');

Insert into FVAL (FUND,FUND_PRED) values ('20001','2000');

Insert into FVAL (FUND,FUND_PRED) values ('20002','2000');

Insert into FVAL (FUND,FUND_PRED) values ('20003','2000');

Insert into FVAL (FUND,FUND_PRED) values ('202118','2000');

Insert into FVAL (FUND,FUND_PRED) values ('2800','280');

Insert into FVAL (FUND,FUND_PRED) values ('280001','2800');

Insert into FVAL (FUND,FUND_PRED) values ('280002','2800');

Insert into FVAL (FUND,FUND_PRED) values ('280003','2800');

Insert into FVAL (FUND,FUND_PRED) values ('280004','2800');

here is a query that works for when I'm doing this for you just one user.

SELECT      F.FUND

FROM        FVAL F

CONNECT BY PRIOR F.FUND    =   F.FUND_PRED

START WITH  F.FUND  IN ( SELECT F_ELEMENT  FROM FSECURE WHERE USERID = 'FFA' );

I would like to make this generic enough to display the element with every user from fsecure.

expected output (Note: I'm only showing one user but I would like to do this for every user on table fsecure)

"FUND""'FFA'"
"140""FFA"
"2000""FFA"
"20001""FFA"
"20002""FFA"
"20003""FFA"
"202118""FFA"
"280""FFA"
"2800""FFA"
"280001""FFA"
"280002""FFA"
"280003""FFA"
"280004""FFA"

Any help would be greatly appreciated.

This post has been answered by Frank Kulash on Oct 28 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 25 2016
Added on Oct 28 2016
19 comments
2,378 views