On 10-Apr-2012 07:45 , Dennis wrote:
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.
create table PRMAST
( id int not null
, r2 for reportto int
, t for title char(5) /* , n for name, etc.... */
, primary key (id) )
Another table, OFFICERS, contains the IDs of Officers within the
organization.
create table OFFICERS
( id for ofrid int not null
, primary key (id) )
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.
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?:
select ID as OFRID from PRMAST
where REPORTTO=(select ID from PRMAST
where ID=REPORTTO) /* reports to Chairman */
and ID<>REPORTTO /* excluding Chairman */
My code examples included later assume "yes" to the above question.
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 possible. I've had
a difficult time envisioning a solution that doesn't involve
rebuilding some intermediate table every time a reporting structure
changes.
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 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)
A TABLE could be created as [an effective] MQT. That data could
serve the same purpose as the above CTE but available to any queries,
generated in the /same/ way; i.e. including only the OFRID and ID to
provide necessary data for a join with PRMAST. This would be similar to
maintaining the reports-to-officer in the physical data, but via delayed
maintenance in a separate physical copy; the maintenance could be
scheduled and\or performed for each [batch] change to the master, or
more immediately with triggers on the original TABLE.
Also consider that instead of a CTE, a VIEW designed the /same/ can
be used by any query [even another VIEW] instead of being scoped to just
the one query... and thus, encapsulated as a VIEW, need be written just
once:
create view PRMAST_RTO /* OFRID gives Reports to Officer */ as
( select GET_OFFICER(ID) AS OFRID, PRMAST.*
from PRMAST
)
Depending on the query and optimization, selection against the
PRMAST_RTO may pare the amount of data against which the UDF must
operate, just as with the CTE, even if not so intuitive; i.e. the effect
of a CTE need not produce the full set of rows, nor even any temporary
result, similar to reference to a VIEW.
That may be our best option (?) but it seems there should be a more
elegant way; it's just not coming to me.
A recursive query as already suggested. However there was no mention
of a "level" included in this scenario, as was assumed in the example
provided with that suggestion, so the query given in that reply would
need to be adjusted.
Any other true real-time solution ideas come to mind here?
Because such a data set is probably not very large, that limits the
number of derivations for GET_OFFICER during reporting, so there may be
little justification for alternate solutions unless the UDF performs
poorly. That could justify seeking alternatives that could perform
better; e.g. for reporting against all data.
IMO either maintaining the data within the existing PRMAST or in an
actively maintained [via SQL TRIGGER definitions] TABLE that correlates
each ID to its GET_OFFICER(ID) return value may be the best approaches
in order to limit the calculation\derivation within reporting. The
assumption being that there is probably limited changes to the data, so
additional maintenance for update activity likely would be minimal,
regardless of whether the new column data would be stored in the
existing table or in a correlation table.
However...
When a specific maximum number of levels is known for any particular
hierarchy, the recursion still can be built into a [view] query, without
the use of the "recursive CTE" concept which is not supported until v5r4.
Assuming four employee levels [three /boss/ levels] under the
Chairman, the following examples can be functional as well as easily
extended to a somewhat larger number; although best when limited to only
a few levels. There is also a hard restriction for the number of files
[dataspaces] referenced in a query [join\union\view] which could limit
the actual capability in depth\level; effectively either ~30 [where 32
was an older limit] or ~250 [where 256 is a newer limit].
Using the CREATE TABLE examples inline to the quoted message, the
following INSERT DML establish some test data that can be used to review
the results of the code examples that follow:
<xmp>
insert into PRMAST values( 0, 0, 'KING')
,(1001, 0, 'VICE1') ,(1002, 0, 'VICE2')
,(2001, 1001, 'PRIN1') ,(2002, 1001, 'PRIN2') ,(2003, 1001, 'PRIN3')
,(2021, 1002, 'DUKE1') ,(2022, 1002, 'DUKE2') ,(2023, 1002, 'DUKE3')
,(3001, 2001, 'EARL1') ,(3002, 2001, 'EARL2') ,(3003, 2001, 'EARL3')
,(3021, 2021, 'COUN1') ,(3022, 2021, 'COUN2') ,(3023, 2021, 'COUN3')
,(4001, 3001, 'VISC1') ,(4002, 3001, 'VISC2') ,(4003, 3001, 'VISC3')
,(4021, 3021, 'BARO1') ,(4022, 3021, 'BARO2') ,(4023, 3021, 'BARO3')
;
insert into OFFICERS values(1001),(1002)
;
</xmp>
The following script effects recursion for which selection can be
performed drilled down only to any particular level of the hierarchy
[UNIONing more data with each successive level], with the deepest level
giving the most complete picture [i.e. including all rows from PRMAST]:
<xmp>
create view PRMASTu0 as -- Chairman
( select P.*, 0 as lvl, id as r2t
from PRMAST P where r2 = id )
;
create view PRMASTu1 as -- OFFICERS; ReportsTo=Chairman
( select P.*, 1 as lvl, r2 as r2t
from PRMAST P where id in (select id from OFFICERS)
union all
select * from PRMASTu0 )
;
create view PRMASTu2 as -- level 02; ReportsTo=Officer
( select P.*, 2 as lvl, r2 as r2t
from PRMAST P where r2 in (select id from OFFICERS)
union all
select * from PRMASTu1 )
;
create view PRMASTu3 as -- level 03; ReportsTo=Level 02
( select P.*, 3 as lvl, (select r2t from PRMASTu2 boss
where P.r2 = boss.ID) as r2t
from PRMAST P where r2 in (select id from PRMASTu2
where lvl = 2)
union all
select * from PRMASTu2 )
;
create view PRMASTu4 as -- level 04; ReportsTo=Level 03
( select P.*, 4 as lvl, (select r2t from PRMASTu3 boss
where P.r2 = boss.ID) as r2t
from PRMAST P where r2 in (select id from PRMASTu3
where lvl = 3)
union all
select * from PRMASTu3 )
; -- etc. to maximum lvl
select * from PRMASTu4
order by ID -- ref. last VIEW as union of all hierarchies
</xmp>
The following script effects selection in a VIEW specific to each
level of the hierarchy, and a final view combines all rows from PRMAST
to give the complete picture of all levels:
<xmp>
create view PRMASTh0 as -- Chairman
( select P.*, 0 as lvl, id as r2t
from PRMAST P where r2 = id )
;
create view PRMASTh1 as -- OFFICERS; ReportsTo=Chairman
( select P.*, 1 as lvl, r2 as r2t
from PRMAST P where id in (select id from OFFICERS) )
;
create view PRMASTh2 as -- level 02; ReportsTo=Officer
( select P.*, 2 as lvl, r2 as r2t
from PRMAST P where r2 in (select id from OFFICERS) )
;
create view PRMASTh3 as -- level 03; ReportsTo=Level 02
( select P.*, 3 as lvl, (select r2t from PRMASTh2 boss
where P.r2 = boss.ID) as r2t
from PRMAST P where r2 in (select id from PRMASTh2) )
;
create view PRMASTh4 as -- level 04; ReportsTo=Level 03
( select P.*, 4 as lvl, (select r2t from PRMASTh3 boss
where P.r2 = boss.ID) as r2t
from PRMAST P where r2 in (select id from PRMASTh3) )
; -- etc.
create view PRMASThU as -- level *ALL; row data UNIONed
( select * from prmasth0 union all
select * from prmasth1 union all
select * from prmasth2 union all
select * from prmasth3 union all
select * from prmasth4 /* etc. to maximum lvl */)
;
select * from PRMASThU
order by ID -- ref. last VIEW as union of all hierarchies
</xmp>
FWiW The following query acquires relationship knowledge across
columns of the result set.
<xmp>
select h1.id, h2.id, h3.id, h4.id /* , etc. */
/* or: h1.*, h2.*, h3.*, h4.* */
from OFFICERS H1
left outer join PRMAST h2
on h1.id=h2.r2
left outer join PRMAST h3
on h2.id=h3.r2
left outer join PRMAST h4
on h3.id=h4.r2
/* etc. to maximum lvl */
</xmp>
However the above query is not very useful for set processing, so
instead, the same concept of explicit joins [the previous VIEWs would
likely implement at least partially as join queries] to generate row
results [but this example chooses to exclude the Chairman]:
<xmp>
create view PRMASTjU as -- Join and Union the row data
( -- include whoever is an Officer first
select p.*, 1 as lvl, p.r2 as r2t
from prmast p where p.id in (select id from officers)
union all -- include whoever Reports-to an Officer
select p.*, 2 as lvl, p.r2 as r2t
from prmast p where p.r2 in (select id from officers)
union all -- include whoever Reports-to a lvl2 Boss
select p.*, 3 as lvl, j.id as r2t
from officers j
inner join prmast b1 on j.id=b1.r2
inner join prmast p on b1.id= p.r2
union all -- include whoever Reports-to a lvl3 Boss
select p.*, 4 as lvl, j.id as r2t
from officers j
inner join prmast b1 on j.id=b1.r2
inner join prmast b2 on b1.id=b2.r2
inner join prmast p on b2.id= p.r2
/* etc. to maximum lvl */
)
;
select * from PRMASTjU
order by ID -- VIEW as union of all hierarchies; uses JOIN
</xmp>
The queries could employ CTEs which would just mimic the VIEW
scenarios already given [but again, their use is scoped to any specific
query, so the VIEWs are often the better choice]:
<xmp>
with
H0 as (select P.*, 0 as lvl, id as r2t
from PRMAST P where r2=id ) -- ReportTo self=Chairman
, H1 as (select P.*, 1 as lvl, r2 as r2t -- ReportTo Chairman
from PRMAST P where r2 = (select id from H0)
and id<>r2 ) -- excluding Chairman
, x1 as (select P.*, 1 as lvl, r2 as r2t -- ReportTo Chairman
from PRMAST P where id in (select ofrid from OFFICERS) )
, H2 as (select P.*, 2 as lvl, r2 as r2t -- ReportTo Officer
from PRMAST P where r2 in (select id from H1) )
, H3 as (select P.*, 3 as lvl, (select r2t from H2 boss
where P.r2 = boss.ID) as r2t
from PRMAST P where r2 in (select id from H2) )
, H4 as (select P.*, 4 as lvl, (select r2t from H3 boss
where P.r2 = boss.ID) as r2t
from PRMAST P where r2 in (select id from H3) )
/* etc. to maximum lvl */
select * from H0 union all
select * from H1 union all
select * from H2 union all
select * from H3 union all
select * from H4 /* etc. to maximum lvl */
; -- all hierarchies as UNION of all the individual CTEs
with
h0 as (select p.*, 0 as lvl, id as r2t
from prmast p where r2=id)
, h1 as (select p.*, 1 as lvl, r2 as r2t
from prmast p where r2=(select id from h0) and id<>r2
union all
select * from h0 )
, h2 as (select p.*, 2 as lvl, r2 as r2t
from prmast p where r2 in (select id from h1 where lvl=1)
union all
select * from h1 )
, h3 as (select p.*, 3 as lvl
, (select r2t from h2 boss where p.r2=boss.id) as r2t
from prmast p where r2 in (select id from h2 where lvl=2)
union all
select * from h2 )
, h4 as (select p.*, 4 as lvl
, (select r2t from h3 boss where p.r2=boss.id) as r2t
from prmast p where r2 in (select id from h3 where lvl=3)
union all
select * from h3 )
/* etc. to maximum lvl */
select * from h4 -- ref. last CTE as UNION of all hierarchies
</xmp>
Regards, Chuck
midrange.com
