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.