Hello ,
Oracle 12c db. (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0)
I need some help in constructing my SQL query.
I am trying to build a relationship diagram and depth diagram based on relationship.
From my data set I am able to came up with PATH Parent child and grand child relationship
now I need to convert this data into FROM (or TO) , so that I can use it to draw the diagram.
So, in the date below 15688 ( relative depth 0)-> 22923 ,128953-57, 8700
15688->22923 ( relative depth -1) -> 8698 , 62977 .....
15688->22923 -> 8698 ( relative depth -2) -> 8698
15688-> 22923 -> 8698 -> 8698->( relative depth -3) ->237
i have attached a sample data set , insert statement
Sample data
CREATE TABLE "RELATIONS" (
"RELN_ID" NUMBER(20, 4)
, "V_CLIENT" NUMBER(20, 4)
, "V_CLIENT_ID_OWNER" NUMBER(20, 4)
, "INDIVIDUAL_ID" NUMBER(20, 4)
, "ENTITY_ID_OWNER" NUMBER(20, 4)
, "ENTITY_ID" NUMBER(20, 4)
, "ASSET" VARCHAR2(1024 BYTE)
, "ASSET_ID" NUMBER(20, 4)
, "ASSET_TYPE" VARCHAR2(8 BYTE)
, "OWNER" VARCHAR2(4000 BYTE)
, "OWNER_ID" NUMBER(20, 4)
, "OWNER_TYPE" VARCHAR2(10 BYTE)
, "RELATIVE_DEPTH" NUMBER
, "OWNERSHIP_PERCENTAGE" VARCHAR2(41 BYTE)
, "RELN_TYPE_FULL_DESP" VARCHAR2(32 BYTE)
, "RELN_TYPE" VARCHAR2(8 BYTE)
, "PATH" VARCHAR2(4000 BYTE)
);
REM INSERTING into RELATIONS
--SET DEFINE OFF;
Insert into RELATIONS (RELN_ID,V_CLIENT,V_CLIENT_ID_OWNER,INDIVIDUAL_ID,ENTITY_ID_OWNER,ENTITY_ID,ASSET,ASSET_ID,ASSET_TYPE,OWNER,OWNER_ID,OWNER_TYPE,RELATIVE_DEPTH,OWNERSHIP_PERCENTAGE,RELN_TYPE_FULL_DESP,RELN_TYPE,PATH) values (119792,15688,22923,null,null,null,'Tllinpay Tinancial Services Limited (CHG)',15688,'V CLIENT','SPARKASSE MECKLENBURG-NORDWEST',22923,'V CLIENT',0,'90%','Client Owns Client','COC','*15688*22923*');
Insert into RELATIONS (RELN_ID,V_CLIENT,V_CLIENT_ID_OWNER,INDIVIDUAL_ID,ENTITY_ID_OWNER,ENTITY_ID,ASSET,ASSET_ID,ASSET_TYPE,OWNER,OWNER_ID,OWNER_TYPE,RELATIVE_DEPTH,OWNERSHIP_PERCENTAGE,RELN_TYPE_FULL_DESP,RELN_TYPE,PATH) values (14182,15688,null,128953,null,null,'Tllinpay Tinancial Services Limited (CHG)',15688,'V CLIENT','Poon Wing Tang',128953,'INDIVIDUAL',0,'%','AML Officer/Equivalent','AML_OFF','*15688*128953*');
Insert into RELATIONS (RELN_ID,V_CLIENT,V_CLIENT_ID_OWNER,INDIVIDUAL_ID,ENTITY_ID_OWNER,ENTITY_ID,ASSET,ASSET_ID,ASSET_TYPE,OWNER,OWNER_ID,OWNER_TYPE,RELATIVE_DEPTH,OWNERSHIP_PERCENTAGE,RELN_TYPE_FULL_DESP,RELN_TYPE,PATH) values (26646,15688,null,128954,null,null,'Tllinpay Tinancial Services Limited (CHG)',15688,'V CLIENT','Tinman Yu',128954,'INDIVIDUAL',0,'0%','Chairman of the Board','COB','*15688*128954*');
Insert into RELATIONS (RELN_ID,V_CLIENT,V_CLIENT_ID_OWNER,INDIVIDUAL_ID,ENTITY_ID_OWNER,ENTITY_ID,ASSET,ASSET_ID,ASSET_TYPE,OWNER,OWNER_ID,OWNER_TYPE,RELATIVE_DEPTH,OWNERSHIP_PERCENTAGE,RELN_TYPE_FULL_DESP,RELN_TYPE,PATH) values (26647,15688,null,128954,null,null,'Tllinpay Tinancial Services Limited (CHG)',15688,'V CLIENT','Tinman Yu',128954,'INDIVIDUAL',0,'5%','Individual Owns Client','IOC','*15688*128954*');
Insert into RELATIONS (RELN_ID,V_CLIENT,V_CLIENT_ID_OWNER,INDIVIDUAL_ID,ENTITY_ID_OWNER,ENTITY_ID,ASSET,ASSET_ID,ASSET_TYPE,OWNER,OWNER_ID,OWNER_TYPE,RELATIVE_DEPTH,OWNERSHIP_PERCENTAGE,RELN_TYPE_FULL_DESP,RELN_TYPE,PATH) values (26648,15688,null,128954,null,null,'Tllinpay Tinancial Services Limited (CHG)',15688,'V CLIENT','Tinman Yu',128954,'INDIVIDUAL',0,'0%','CEO/Equivalent','CEO','*15688*128954*');
Insert into RELATIONS (RELN_ID,V_CLIENT,V_CLIENT_ID_OWNER,INDIVIDUAL_ID,ENTITY_ID_OWNER,ENTITY_ID,ASSET,ASSET_ID,ASSET_TYPE,OWNER,OWNER_ID,OWNER_TYPE,RELATIVE_DEPTH,OWNERSHIP_PERCENTAGE,RELN_TYPE_FULL_DESP,RELN_TYPE,PATH) values (26640,15688,null,128955,null,null,'Tllinpay Tinancial Services Limited (CHG)',15688,'V CLIENT','Ti Lan',128955,'INDIVIDUAL',0,'0%','Board of Director','BOD','*15688*128955*');
Insert into RELATIONS (RELN_ID,V_CLIENT,V_CLIENT_ID_OWNER,INDIVIDUAL_ID,ENTITY_ID_OWNER,ENTITY_ID,ASSET,ASSET_ID,ASSET_TYPE,OWNER,OWNER_ID,OWNER_TYPE,RELATIVE_DEPTH,OWNERSHIP_PERCENTAGE,RELN_TYPE_FULL_DESP,RELN_TYPE,PATH) values (26641,15688,null,128955,null,null,'Tllinpay Tinancial Services Limited (CHG)',15688,'V CLIENT','Ti Lan',128955,'INDIVIDUAL',0,'5%','Individual Owns Client','IOC','*15688*128955*');
Insert into RELATIONS (RELN_ID,V_CLIENT,V_CLIENT_ID_OWNER,INDIVIDUAL_ID,ENTITY_ID_OWNER,ENTITY_ID,ASSET,ASSET_ID,ASSET_TYPE,OWNER,OWNER_ID,OWNER_TYPE,RELATIVE_DEPTH,OWNERSHIP_PERCENTAGE,RELN_TYPE_FULL_DESP,RELN_TYPE,PATH) values (18628,15688,null,128956,null,null,'Tllinpay Tinancial Services Limited (CHG)',15688,'V CLIENT','Tianhua Wan',128956,'INDIVIDUAL',0,'%','Board of Director','BOD','*15688*128956*');
Insert into RELATIONS (RELN_ID,V_CLIENT,V_CLIENT_ID_OWNER,INDIVIDUAL_ID,ENTITY_ID_OWNER,ENTITY_ID,ASSET,ASSET_ID,ASSET_TYPE,OWNER,OWNER_ID,OWNER_TYPE,RELATIVE_DEPTH,OWNERSHIP_PERCENTAGE,RELN_TYPE_FULL_DESP,RELN_TYPE,PATH) values (18290,15688,null,128957,null,null,'Tllinpay Tinancial Services Limited (CHG)',15688,'V CLIENT','Li Zhu',128957,'INDIVIDUAL',0,'%','Board of Director','BOD','*15688*128957*');
Insert into RELATIONS (RELN_ID,V_CLIENT,V_CLIENT_ID_OWNER,INDIVIDUAL_ID,ENTITY_ID_OWNER,ENTITY_ID,ASSET,ASSET_ID,ASSET_TYPE,OWNER,OWNER_ID,OWNER_TYPE,RELATIVE_DEPTH,OWNERSHIP_PERCENTAGE,RELN_TYPE_FULL_DESP,RELN_TYPE,PATH) values (119416,15688,null,null,8700,null,'Tllinpay Tinancial Services Limited (CHG)',15688,'V CLIENT','ALM Brand A/S',8700,'ENTITY',0,'%','Chairman of the Board','COB','*15688*8700*');
Insert into RELATIONS (RELN_ID,V_CLIENT,V_CLIENT_ID_OWNER,INDIVIDUAL_ID,ENTITY_ID_OWNER,ENTITY_ID,ASSET,ASSET_ID,ASSET_TYPE,OWNER,OWNER_ID,OWNER_TYPE,RELATIVE_DEPTH,OWNERSHIP_PERCENTAGE,RELN_TYPE_FULL_DESP,RELN_TYPE,PATH) values (34297,22923,null,53744,null,null,'SPTRKTSSE MECKLEMBURG-MORDWEST',22923,'V CLIENT','Birte Mahnert',53744,'INDIVIDUAL',-1,'%','AML Officer/Equivalent','AML_OFF','*15688*22923**53744*');
Insert into RELATIONS (RELN_ID,V_CLIENT,V_CLIENT_ID_OWNER,INDIVIDUAL_ID,ENTITY_ID_OWNER,ENTITY_ID,ASSET,ASSET_ID,ASSET_TYPE,OWNER,OWNER_ID,OWNER_TYPE,RELATIVE_DEPTH,OWNERSHIP_PERCENTAGE,RELN_TYPE_FULL_DESP,RELN_TYPE,PATH) values (34298,22923,null,54604,null,null,'SPTRKTSSE MECKLEMBURG-MORDWEST',22923,'V CLIENT','Manuel Krastel',54604,'INDIVIDUAL',-1,'%','CEO/Equivalent','CEO','*15688*22923**54604*');
Insert into RELATIONS (RELN_ID,V_CLIENT,V_CLIENT_ID_OWNER,INDIVIDUAL_ID,ENTITY_ID_OWNER,ENTITY_ID,ASSET,ASSET_ID,ASSET_TYPE,OWNER,OWNER_ID,OWNER_TYPE,RELATIVE_DEPTH,OWNERSHIP_PERCENTAGE,RELN_TYPE_FULL_DESP,RELN_TYPE,PATH) values (34299,22923,null,54604,null,null,'SPTRKTSSE MECKLEMBURG-MORDWEST',22923,'V CLIENT','Manuel Krastel',54604,'INDIVIDUAL',-1,'%','Chairman of the Board','COB','*15688*22923**54604*');
Insert into RELATIONS (RELN_ID,V_CLIENT,V_CLIENT_ID_OWNER,INDIVIDUAL_ID,ENTITY_ID_OWNER,ENTITY_ID,ASSET,ASSET_ID,ASSET_TYPE,OWNER,OWNER_ID,OWNER_TYPE,RELATIVE_DEPTH,OWNERSHIP_PERCENTAGE,RELN_TYPE_FULL_DESP,RELN_TYPE,PATH) values (34300,22923,null,62997,null,null,'SPTRKTSSE MECKLEMBURG-MORDWEST',22923,'V CLIENT','Kay Facklam',62997,'INDIVIDUAL',-1,'%','Board of Director','BOD','*15688*22923**62997*');
Insert into RELATIONS (RELN_ID,V_CLIENT,V_CLIENT_ID_OWNER,INDIVIDUAL_ID,ENTITY_ID_OWNER,ENTITY_ID,ASSET,ASSET_ID,ASSET_TYPE,OWNER,OWNER_ID,OWNER_TYPE,RELATIVE_DEPTH,OWNERSHIP_PERCENTAGE,RELN_TYPE_FULL_DESP,RELN_TYPE,PATH) values (120040,22923,null,null,8698,null,'SPTRKTSSE MECKLEMBURG-MORDWEST',22923,'V CLIENT','Misr Capital Investments S.A.E.',8698,'ENTITY',-1,'80%','Entity Owns Client','EOC','*15688*22923**8698*');
Insert into RELATIONS (RELN_ID,V_CLIENT,V_CLIENT_ID_OWNER,INDIVIDUAL_ID,ENTITY_ID_OWNER,ENTITY_ID,ASSET,ASSET_ID,ASSET_TYPE,OWNER,OWNER_ID,OWNER_TYPE,RELATIVE_DEPTH,OWNERSHIP_PERCENTAGE,RELN_TYPE_FULL_DESP,RELN_TYPE,PATH) values (120046,null,null,79503,null,237,'1st Tdvantage Bancshares, Onc.',237,'ENTITY','A. Bruce Leadbeater',79503,'INDIVIDUAL',-3,'80%','Individual Owns Entity','IOE','*15688*22923**8698**237**79503*');
Insert into RELATIONS (RELN_ID,V_CLIENT,V_CLIENT_ID_OWNER,INDIVIDUAL_ID,ENTITY_ID_OWNER,ENTITY_ID,ASSET,ASSET_ID,ASSET_TYPE,OWNER,OWNER_ID,OWNER_TYPE,RELATIVE_DEPTH,OWNERSHIP_PERCENTAGE,RELN_TYPE_FULL_DESP,RELN_TYPE,PATH) values (120053,null,null,79503,null,237,'1st Tdvantage Bancshares, Onc.',237,'ENTITY','A. Bruce Leadbeater',79503,'INDIVIDUAL',-3,'20%','Chairman of the Board','COB','*15688*22923**8698**237**79503*');
Insert into RELATIONS (RELN_ID,V_CLIENT,V_CLIENT_ID_OWNER,INDIVIDUAL_ID,ENTITY_ID_OWNER,ENTITY_ID,ASSET,ASSET_ID,ASSET_TYPE,OWNER,OWNER_ID,OWNER_TYPE,RELATIVE_DEPTH,OWNERSHIP_PERCENTAGE,RELN_TYPE_FULL_DESP,RELN_TYPE,PATH) values (120043,null,null,null,237,8698,'Misr Capital Onvestments S.T.E.',8698,'ENTITY','1st Advantage Bancshares, Inc.',237,'ENTITY',-2,'98%','Entity Owns Entity','EOE','*15688*22923**8698**237*');
Insert into RELATIONS (RELN_ID,V_CLIENT,V_CLIENT_ID_OWNER,INDIVIDUAL_ID,ENTITY_ID_OWNER,ENTITY_ID,ASSET,ASSET_ID,ASSET_TYPE,OWNER,OWNER_ID,OWNER_TYPE,RELATIVE_DEPTH,OWNERSHIP_PERCENTAGE,RELN_TYPE_FULL_DESP,RELN_TYPE,PATH) values (120291,null,null,63378,null,8700,'TLM Brand T/S',8700,'ENTITY','A. Tohn May',63378,'INDIVIDUAL',-1,'%','AML Officer/Equivalent','AML_OFF','*15688*8700**63378*');
/
Create_isnert_table.txt (8.47 KB)