CODEWITHSUNDEEP
sqloracle-database
Miguel
Miguel

Reputation: 2079

Join Hierarchy By Multiple conditions

I have 3 tables one contains users and the elements that are assigned to those users. Table DDL looks like this.

  CREATE TABLE FSECURE
   (    "USERID" VARCHAR2(100 BYTE), 
    "F_ELEMENT" VARCHAR2(6 BYTE), 
    "O_ELEMENT" VARCHAR2(20 BYTE), 
    "TYPE" VARCHAR2(20 BYTE)
   );

The data that table contains looks like this .

   REM INSERTING into FSECURE
SET DEFINE OFF;
Insert into FSECURE (USERID,F_ELEMENT,O_ELEMENT,TYPE) values ('FFA','140','2003','APP1');
Insert into FSECURE (USERID,F_ELEMENT,O_ELEMENT,TYPE) values ('FFA','280','2003','APP1');
Insert into FSECURE (USERID,F_ELEMENT,O_ELEMENT,TYPE) values ('GGA','202118','2003','APP2');
Insert into FSECURE (USERID,F_ELEMENT,O_ELEMENT,TYPE) values ('GGA','140','2004','APP2');
Insert into FSECURE (USERID,F_ELEMENT,O_ELEMENT,TYPE) values ('RFA','202116','3662','APP3');
Insert into FSECURE (USERID,F_ELEMENT,O_ELEMENT,TYPE) values ('FFA','110001','3662','APP3');

The fvol table contains the reference to the o_element column for the f_secure table.

 CREATE TABLE FVOL
       (    "ORG" VARCHAR2(6 BYTE), 
        "ORG_PRED" VARCHAR2(6 BYTE)
       );

REM INSERTING into FVOL
SET DEFINE OFF;
Insert into FVOL (ORG,ORG_PRED) values ('2003',null);
Insert into FVOL (ORG,ORG_PRED) values ('3661','2003');
Insert into FVOL (ORG,ORG_PRED) values ('3662','2003');
Insert into FVOL (ORG,ORG_PRED) values ('2009',null);
Insert into FVOL (ORG,ORG_PRED) values ('366X','2009');
Insert into FVOL (ORG,ORG_PRED) values ('366P','2009');
Insert into FVOL (ORG,ORG_PRED) values ('2004',null);
Insert into FVOL (ORG,ORG_PRED) values ('1114','2004');
Insert into FVOL (ORG,ORG_PRED) values ('2224','2004');

The fval table contains the reference to the f_element in the fsecure table.

CREATE TABLE "FVAL" 
   (    "FUND" VARCHAR2(6 BYTE), 
    "FUND_PRED" VARCHAR2(6 BYTE)
   );



    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');
Insert into FVAL (FUND,FUND_PRED) values ('110001','11000');
Insert into FVAL (FUND,FUND_PRED) values ('110002','11000');

The problem that I'm having is that I need to traverse the hierarchy by user - f_element - o_element - type

So for example in the case of user_id FFA which has hierarchy node in the fsecure table as F_ELEMENT = '140' AND O_ELEMENT = '2003' AND TYPE APP1 just for that row I should retrieve sample output:

FUND:     ORG:          TYPE:
20001     3661          APP1
20002     3661          APP1
20003     3661          APP1
202118    3661          AAP1

20001     3662         AAP1
20002     3662         AAP1
20003     3663         APP1
202118    3663         AAP1
110001    3662         AAP3

The issue that I'm having is traversing the hierarchy type, userid, f_element and o_element. I can traverse the hierarchy but I'm having a mental block on how do it without combining the elements. Note: That FFA USER FOR APP3 only gets one f_element and o_element since he does not have 140 and 2003 as part of that row.

Upvotes: 0

Views: 74

Answers (1)

Ramesh
Ramesh

Reputation: 1484

@Miguel, Here is a solution using CONNECT BY query

Data

  CREATE TABLE FSECURE
   (    "USERID" VARCHAR2(100 BYTE), 
    "F_ELEMENT" VARCHAR2(6 BYTE), 
    "O_ELEMENT" VARCHAR2(20 BYTE), 
    "TYPE" VARCHAR2(20 BYTE)
   ); 
Insert into FSECURE (USERID,F_ELEMENT,O_ELEMENT,TYPE) values ('FFA','140','2003','APP1');
Insert into FSECURE (USERID,F_ELEMENT,O_ELEMENT,TYPE) values ('FFA','280','2003','APP1');
Insert into FSECURE (USERID,F_ELEMENT,O_ELEMENT,TYPE) values ('GGA','202118','2003','APP2');
Insert into FSECURE (USERID,F_ELEMENT,O_ELEMENT,TYPE) values ('GGA','140','2004','APP2');
Insert into FSECURE (USERID,F_ELEMENT,O_ELEMENT,TYPE) values ('RFA','202116','3662','APP3');
Insert into FSECURE (USERID,F_ELEMENT,O_ELEMENT,TYPE) values ('FFA','110001','3662','APP3');

CREATE TABLE FVOL
       (    "ORG" VARCHAR2(6 BYTE), 
        "ORG_PRED" VARCHAR2(6 BYTE)
       );
Insert into FVOL (ORG,ORG_PRED) values ('2003',null);
Insert into FVOL (ORG,ORG_PRED) values ('3661','2003');
Insert into FVOL (ORG,ORG_PRED) values ('3662','2003');
Insert into FVOL (ORG,ORG_PRED) values ('2009',null);
Insert into FVOL (ORG,ORG_PRED) values ('366X','2009');
Insert into FVOL (ORG,ORG_PRED) values ('366P','2009');
Insert into FVOL (ORG,ORG_PRED) values ('2004',null);
Insert into FVOL (ORG,ORG_PRED) values ('1114','2004');
Insert into FVOL (ORG,ORG_PRED) values ('2224','2004');



CREATE TABLE "FVAL" 
   (    "FUND" VARCHAR2(6 BYTE), 
    "FUND_PRED" VARCHAR2(6 BYTE)
   );
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');
Insert into FVAL (FUND,FUND_PRED) values ('110001','11000');
Insert into FVAL (FUND,FUND_PRED) values ('110002','11000');

Solution

WITH v_fval
AS
(SELECT fund, r_fund
   FROM (SELECT fund, 
               connect_by_root fund AS r_fund,
               connect_by_isleaf AS l_fund
          FROM fval 
         START WITH fund_pred IS NULL
         CONNECT BY PRIOR fund = fund_pred)
  WHERE l_fund = 1)
, v_fvol 
AS
(SELECT org, r_org
   FROM (SELECT org, 
               connect_by_root org AS r_org,
               connect_by_isleaf AS l_org
          FROM fvol 
          START WITH org_pred IS NULL
          CONNECT BY PRIOR org = org_pred)
  WHERE l_org = 1)
SELECT fsecure.userid, 
       NVL(v_fvol.org, fsecure.o_element) AS org,
       NVL(v_fval.fund, fsecure.f_element) AS fund, 
       fsecure.type
  FROM fsecure LEFT JOIN v_fvol 
                 ON (fsecure.o_element = v_fvol.r_org)
               LEFT JOIN v_fval 
                 ON (fsecure.f_element = v_fval.r_fund)
 WHERE fsecure.userid = 'FFA'
 ORDER BY fsecure.userid, v_fvol.org, v_fval.fund  ;

Output is as below

USERID  ORG FUND    TYPE
FFA 3661    20001   APP1
FFA 3661    20002   APP1
FFA 3661    20003   APP1
FFA 3661    202118  APP1
FFA 3661    280001  APP1
FFA 3661    280002  APP1
FFA 3661    280003  APP1
FFA 3661    280004  APP1
FFA 3662    20001   APP1
FFA 3662    20002   APP1
FFA 3662    20003   APP1
FFA 3662    202118  APP1
FFA 3662    280001  APP1
FFA 3662    280002  APP1
FFA 3662    280003  APP1
FFA 3662    280004  APP1
FFA 3662    110001  APP3

Explanation.

The 2 views get the leaf node of the hierarchy and the main query joins them up. I would suggest running the views separately to understand better.

SQL Fiddle Demo here - http://sqlfiddle.com/#!4/a2a751/23

Upvotes: 1

Related Questions