troubleshooting Question

Oracle -- Multiple SELECT statement ?

Avatar of finance_teacher
finance_teacher asked on
Oracle Database
4 Comments1 Solution812 ViewsLast Modified:
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);
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros