Oracle -- Multiple SELECT statement ?

finance_teacher
finance_teacher used Ask the Experts™
on
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);
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
where are you expecting to get the employee titles from?

that is,  how do we know   101/3005 is a  MANAGER  from the data provided?
Most Valuable Expert 2011
Top Expert 2012
Commented:
the hierarchy of positions can be built with a CONNECT BY


SELECT     LPAD(' ', (LEVEL - 1) * 3) || pos_code
      FROM company_position_structure_tab t
CONNECT BY PRIOR pos_code = sup_pos_code
START WITH sup_pos_code = '*'
Most Valuable Expert 2011
Top Expert 2012

Commented:
if titles are determined by position in the hierarchy then try this...


SELECT     LPAD(' ', (LEVEL - 1) * 3) || pos_code
           || CASE
                  WHEN LEVEL = 1 THEN ' PRESIDENT'
                  WHEN CONNECT_BY_ISLEAF = 1 THEN ' EMPLOYEE'
                  ELSE ' MANAGER'
              END
               employee_hierarchy
      FROM company_position_structure_tab t
CONNECT BY PRIOR pos_code = sup_pos_code
START WITH sup_pos_code = '*'

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial