RE: Finding reporting officer

Thank you, Chuck; you obviously put significant time on this.

Is the OFFICERS table just a separate list of ID values that defines
the highest level of the hierarchy immediately under the Chairman, for
which each OFRID would have a REPORTTO the Chairman ID in the PRMAST?
That is, these "officer" IDs still track their PR data in the PRMAST,
just like each successive level in the hierarchy? Thus the OFFICERS
table is the same as the following query?:

Actually, it's a bit more convoluted than that. There is no OFFICERS table,
per se; the actual infrastructure has a board positions table (cleverly
disguised as BOARDPOS) that contains a position ID code (not employee ID)
which has an indicator column LDRCMTEE which has '1' if the _position_ is on
the leadership committee. Then there is a BOARDMBR table that contains,
among other things, the employee ID and the board position ID in one place.
So the following works:

SELECT
FROM PRMAST PR
JOIN BOARDMBR BM on BM.ID = PR.ID
JOIN BOARDPOS BP on BP.POSNID = BM.POSNID
And BP.LDRCMTEE = '1'

... and that is how OFFICERS is derived. That's OK at one level (with
today's DB speeds and only 2500 employees or thereabouts, it's almost as
good as a table)... but there are nine levels today (answering another of
your questions) and I'm afraid performance might suffer significantly... and
my head hurts when I think of the dependencies and possible maintenance
involved with your thoughtful PRMASTu* and PRMASTh* below. It's a great
solution for an environment as simple as I represented, but alas.

If such derived information is routinely required for reporting, a
new column REPORTTO_OFR could be maintained in the physical data to
avoid the /calculated/ value.? Triggers could ensure the integrity of
the new column for the various I\O. A logical file excluding the new
column could even replace the physical [in name only], to reduce impact
to existing applications dependent on the original PF record format.

I also like your solution of enhancing an external-table solution with a
trigger (that would have occurred to me eventually), and that's the solution
my management seems to like from the options presented.

Thanks.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"In the game of life it's a good idea to have a few early losses, which
relieves you of the pressure of trying to maintain an undefeated season."
-- Bill Vaughan