What you want is a recursive CTE. The following is from this pdf that
was release when v5r4 came out.
http://www-03.ibm.com/systems/resources/systems_i_software_db2_pdf_rcte_
olap.pdf
WITH emp_list (level, empid, name) AS
(SELECT 1, empid, name FROM emp WHERE name = 'Carfino'
UNION ALL
SELECT o.level + 1, next_layer.empid, next_layer.name
FROM emp as next_layer, emp_list o
WHERE o.empid = next_layer.mgrid )
SELECT level, name FROM emp_list
-Tom Stieger
IT Manager
California Fine Wire
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dennis
Sent: Tuesday, April 10, 2012 7:46 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Finding reporting officer
Hello, fellow techies:
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?
Thanks,
++
Dennis
++
Sent from my Galaxy tablet phone. Please excuse my brevity.
For any grammatic/spelling errors, there is no excuse.
++
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
