In our personnel master, PRMAST, we have a column, REPORTTO, that contains the ID of the individual's supervisor, foreman, lead, whatever next-higher-up individual. Another table, OFFICERS, contains the IDs of Officers within the organization. It is possible to follow the chain of EMP.REPORTTO->REPORTTO->REPORTTO->. . . to eventually reach each individual's reporting officer (when REPORTTO join against OFFICERS.OFRID would be successful). (The top-level individual, Chairman of the Board, has REPORTTO = ID.)
At present, we have an RPG module that resolves reporting officer, and it is also used in a UDF so that we can include OFRID to reports, etc. I have been asked to look into the possibility of creating a view that efficiently resolves this so that headcount and costing reports and the like - by officer code - would be pissible. I've had a difficult time envisioning a solution that doesn't involve rebuilding some intermediate table every time a reporting structure changes.
I have considered CTE like:
WITH T1 AS (SELECT ID, GET_OFFICER(ID) AS OFRID, . . . FROM PRMAST. . .)
SELECT T1.OFRID, T1.ID, . . . FROM T1 ORDER BY OFRID)
That may be our best option (?) but it seems there should be a more elegant way; it's just not coming to me.
Any other true real-time solution ideas come to mind here?
Sent from my Galaxy tablet phone. Please excuse my brevity.
For any grammatic/spelling errors, there is no excuse.
This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact