The below SELECT displays the EMPLOYEE's MANAGER.
SELECT SUP_POS_CODE FROM COMPANY_POSITION
where POS_CODE = '101/3802'
What SELECT or PL/SQL will allow me to
enter '101/3802' and get the below, indented ?
100/1000 PRESIDENT
101/3005 MANAGER
101/3802 EMPLOYEE
........ there will be more layers
depending on who is picked
-----------------------------------------------------------------------------
CREATE TABLE "COMPANY_POSITION_STRUCTURE_TAB"
( "COMPANY_ID" VARCHAR2(20 CHAR),
"POS_CODE" VARCHAR2(10 CHAR),
"VALID_TO" DATE,
"VALID_FROM" DATE,
"SUP_POS_CODE" VARCHAR2(10 CHAR),
"ALTKEY" VARCHAR2(100 CHAR),
"ROWVERSION" NUMBER DEFAULT 1
) ;
Insert into "company_position_structure_tab"
(COMPANY_ID,POS_CODE,VALID_TO,VALID_FROM,SUP_POS_CODE,ALTKEY,ROWVERSION) values
('10','100/1000',to_timestamp('12/31/9999','MM/DD/YYYY HH.MI.SSXFF AM'),to_timestamp('01/01/0001','MM/DD/YYYY
HH.MI.SSXFF AM'),'*','10^100/1000^99991231',1);
Insert into "company_position_structure_tab"
(COMPANY_ID,POS_CODE,VALID_TO,VALID_FROM,SUP_POS_CODE,ALTKEY,ROWVERSION) values
('10','101/3005',to_timestamp('12/31/9999','MM/DD/YYYY HH.MI.SSXFF AM'),to_timestamp('03/11/2011','MM/DD/YYYY
HH.MI.SSXFF AM'),'100/1000','10^101/3005^99991231',1);
Insert into "company_position_structure_tab"
(COMPANY_ID,POS_CODE,VALID_TO,VALID_FROM,SUP_POS_CODE,ALTKEY,ROWVERSION) values
('10','101/3005',to_timestamp('03/10/2011','MM/DD/YYYY HH.MI.SSXFF AM'),to_timestamp('01/01/0001','MM/DD/YYYY
HH.MI.SSXFF AM'),'196/1001','10^101/3005^20110310',2);
INSERT INTO "company_position_structure_tab"
(COMPANY_ID,POS_CODE,VALID_TO,VALID_FROM,SUP_POS_CODE,ALTKEY,ROWVERSION) VALUES
('10','101/3802',TO_TIMESTAMP('12/31/9999','MM/DD/YYYY HH.MI.SSXFF AM'),TO_TIMESTAMP('01/01/0001','MM/DD/YYYY
HH.MI.SSXFF AM'),'101/3005','10^101/3802^99991231',1);