Avatar of finance_teacher
finance_teacher

asked on 

Oracle -- Multiple SELECT statement ?

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);
Oracle Database

Avatar of undefined
Last Comment
finance_teacher

8/22/2022 - Mon