Ah, thanks so much, Tom.

I should have mentioned we are at V5R3 (I know, I know). That didn't stop me from trying, though. :)

Sadly, the effort was honored by message SQL0346: "Recursion not allowed for cimmon table expressions." I will go withe the previously-mentioned CTE, which will work, I am sure.

I do appreciate the reply.
++
Dennis
++
"The radical of one century is the conservative of the next. The radical invents the views. When he has worn them out the conservative adopts them."
-- Mark Twain


Sent from my Galaxy tablet phone. Please excuse my brevity.
For any grammatic/spelling errors, there is no excuse.
++


Tom E Stieger <testieger@xxxxxxxxxxxx> wrote:

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.


This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 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 [javascript protected email address].