Oracle 11g CREATE VIEW using CONNECT BY and multiple tables

Question

Using Oracle 11G (not R2) database, we need to create reports that show which Leadership Committe an individual's reporting structure belongs to.

At a high level, we determine an individual's presence on the board by locating the employee ID in table board_members.

The board_members table has a position ID that can be used to access board_positions, and from there we can determine if the position is on the leadership committee. (Samples below.)

For any employee who is on the leadership committee, his own ID will represent BOARD_LEAD.

For any other employee, the report_to value is recursed until a Leadership Committee member is identified, and that person's ID will be BOARD_LEAD.

Our top-level staff have report_to equal their own empl_id, rather than the more-common NULL.

To hopefully demonstrate this, I have set up sample tables, sample data, and sample desired output below.

I am trying to understand how to create a VIEW that will provide such information for Full Time Equivalence and other reporting needs. I am convinced that CONNECT BY will be involved, but I find the Oracle documentation confusing and I have found no examples that include multiple tables such as this. (I fear there is good reason for this lack of example.)

Is it even possible to write such a view at Oracle 11g (not R2) rather than an intermediate table that must be updated with each position change?

Create table board_positions /* If board_position = 'LDRSHPCOMM' this is a top position */
(member_id varchar(6),board_position varchar(18));

Create table board_members 
(empl_id varchar(6), member_id varchar(6));

Create table emp 
(empl_id varchar(6),ename varchar(32),report_to varchar(6));

Insert into board_positions values('CEO','LDRSHPCOMM');
Insert into board_positions Values('COO','LDRSHPCOMM');
Insert into board_positions Values('CFO','LDRSHPCOMM');
Insert into board_positions Values('CIO','LDRSHPCOMM');
Insert into board_positions values('WANABE','NEWBIE');

Insert into emp ('TOPDOG','Big Guy','TOPDOG');
Insert into emp ('WALLET','Money Bags','TOPDOG');
Insert into emp ('OPSGUY','Meikut Work','TOPDOG');
Insert into emp ('INFGUY','Comp U Turk','TOPDOG');
Insert into emp ('HITECH','Number 2','INFGUY');
Insert into emp ('LOTECH','Number 3','HITECH');
Insert into emp ('PROGMR','Nameless Blameless','LOTECH');
insert into emp ('FLUNKY','Ida Dunnit','PROGMR');

Insert into board_members ('TOPDOG','CEO');
Insert into board_members ('WALLET','CFO');
Insert into board_members ('OPSGUY','COO');
Insert into board_members ('INFGUY','CIO');
Insert into board_members ('HITECH','WANABE');  /* Board position not on the leadership committee */

Using something like:

CREATE VIEW LEADER_VIEW AS
   WITH T1 AS (SELECT e.empl_id, (something) as board_lead
                , (something) as board_lead_pos
           FROM emp e
           LEFT OUTER JOIN board_members bm
                        ON bm.empl_id = e.empl_id
           LEFT OUTER JOIN board_positions bp
                        on bp.member_id = bm.member_id
           ...
           CONNECT BY PRIOR empl_id = report_to
           START WITH empl_id = report_to
           )
  SELECT * FROM T1

(But I know there's much more to it than this!)

Desired output example . . .

TOPDOG         TOPDOG  CEO  (Because self is on LDRSHPCOMM)
WALLET         WALLET  CFO  (Because self is on LDRSHPCOMM)
OPSGUY         OPSGUY  COO  (Because self is on LDRSHPCOMM)
INFGUY         INFGUY  CIO  (Because self is on LDRSHPCOMM)
HITECH         INFGUY  CIO  (Because REPORTTO is on LDRSHPCOMM)
LOTECH         INFGUY  CIO  (Because REPORTTO->REPORTTO is on LDRSHPCOMM)
PROGMR         INFGUY  CIO  (REPORTTO->REPORTTO->REPORTTO is on LDRSHPCOMM)
FLUNKY         INFGUY  CIO  (You know by now.)

Answer 1

You could do something like this:

SQL> SELECT *
  2    FROM (SELECT empl_id, ename, report_to,
  3                 member_id, board_position,
  4                 MAX(lvl) over(PARTITION BY empl_id) maxlvl, lvl
  5             FROM (SELECT connect_by_root(e.empl_id) empl_id,
  6                          connect_by_root(e.ename) ename,
  7                          bm.empl_id report_to,
  8                          LEVEL lvl, bp.*
  9                      FROM emp e
 10                      LEFT JOIN board_members bm
 11                             ON e.empl_id = bm.empl_id
 12                      LEFT JOIN board_positions bp
 13                             ON bm.member_id = bp.member_id
 14                    CONNECT BY NOCYCLE e.empl_id = PRIOR e.report_to
 15                           AND (PRIOR bp.board_position IS NULL
 16                                OR PRIOR bp.board_position != 'LDRSHPCOMM')))
 17   WHERE lvl = maxlvl;

EMPL_ID ENAME                            REPORT_TO MEMBER_ID BOARD_POSITION     
------- -------------------------------- --------- --------- ------------------ 
FLUNKY  Ida Dunnit                       INFGUY    CIO       LDRSHPCOMM         
HITECH  Number 2                         INFGUY    CIO       LDRSHPCOMM         
INFGUY  Comp U Turk                      INFGUY    CIO       LDRSHPCOMM         
LOTECH  Number 3                         INFGUY    CIO       LDRSHPCOMM         
OPSGUY  Meikut Work                      OPSGUY    COO       LDRSHPCOMM         
PROGMR  Nameless Blameless               INFGUY    CIO       LDRSHPCOMM         
TOPDOG  Big Guy                          TOPDOG    CEO       LDRSHPCOMM         
WALLET  Money Bags                       WALLET    CFO       LDRSHPCOMM      

I have no START WITH clause because I want to start the hierarchical query for all employees. For each employee I walk through the hierarchical data until I find a manager that is a board member on the leadership committee (CONNECT BY clause).

The outer queries filter the relevant lines.