hierarchical query using M:N self-related table

[navrsalemile]

I have the following probnlem: there are two tables used in the report, JOB and TASK related as 1:N.

job_num, open_dte, close_dte JOB table

task_code, job_num TASK table

Jobs are dynamically changed by adding a new record into job table and keeping old records in the same table (there are two date fields, open_dte and close_dte. When close_dte is set to a non-null value that means the record was split or merged. Merging means there can be many parents in JOB table, each contributing it's corresponding TASK links to the newly created JOB record. Splitting means that some links from TASK to JOB table are reassigned to newly created JOB record, i.e. there can be many children in JOB table).
How should query look like in order to present history of a job change, e.g. starting with a job ID 12345 and presenting all splits and/or mergers in hierarchical way, sorted by date?
I tried using start with/connect by but this only works on tree structures while in this case JOB table is like a graph (m:n associations)

many thanks,
Mile

 

[nagornyi]

Would be helpful to see examples of JOB and TASK records and corresponding report output

 

[navrsalemile]

here is the example of data:

JOB table
job_id open closed splitfrom mergedto
=================================================
AAAA 1/1/90 1/1/00
BBBB 1/1/80 1/1/00
CCCC 1/1/00 1/1/01 AAAA EEEE
DDDD 1/1/00 1/1/01 BBBB EEEE
EEEE 1/1/01
FFFF 1/1/01 CCCC

TASK table
job_id task_code modified_on
===================================
AAAA A1 1/1/90
AAAA A2 1/1/91
AAAA A3 1/1/92
BBBB B1 1/1/90
BBBB B2 1/1/90
CCCC A1 1/1/00
CCCC A2 1/1/00
DDDD B1 1/1/00
DDDD B2 1/1/00
EEEE A2 1/1/01
EEEE B1 1/1/01
EEEE B2 1/1/01
FFFF A1 1/1/01

The report should consider each job_id as a continuum, i.e. whatever information is presented about a job_id (e.g. number of tasks for job EEEE in different periods) it must take into account previous job IDs from which this job_id was derived (in case of EEEE, these previous job ids are CCCC and DDDD (because of merge operation), AAAA (CCCC split from) and BBBB (DDDD split from).
An example for job_id EEEE could be:

JobHistory Trend
===========================================================

AAAA/ 6 **before split***
AAAA/CCCC 3 **after split***before merge
BBBB/ 5 **before split***
BBBB/DDDD 7 **after split***before merge
[AAAA/CCCC,BBBB/DDDD]/EEEE 10 ***after merge***

This is an example from Oracle 9i manual to produce tree hierarchy with '/' path which I try to use:

select lpad(' ', 2*level-1)||sys_connect_by_path(last_name, '/') "Path"
from employees
start with last_name = 'Kochhar'
connect by prior employee_id = manager_id;

 

[nagornyi]

In the Trend column, what do the numbers mean and how did you calculate them?

 

[navrsalemile]

To simplify let us just assume there is only JOB table and there is no Trend column in the report.

So we only need to produce this row in the report if the parameter given is EEEE for example:


[AAAA/CCCC,BBBB/DDDD]/EEEE

or just

[/AAAA/CCCC + /BBBB/DDDD]/EEEE


interpretation being the following: record AAAA was split into record CCCC, the record BBBB was split into record DDDD, then these two were merged into EEEE.


If the parameter given is FFFF the the report would look like:

/AAAA/CCCC/FFFF


and the interpretation is: first record AAAA is split into CCCC and then record CCCC is split into FFFF

and so on...

 

[nagornyi]

So far I have only partial solution for your problem. I know how to build "splitfrom" chain, but not "mergeto" chain. To build the first one, you may use the query:

select 
nvl(nvl(job3.job_id, job2.job_id), job1.job_id) job_id
,
replace( 
replace( 
job1.splitfrom||'/'||job2.splitfrom||'/'||job3.splitfrom||'/'||job3.job_id 
,'//','/') 
,'//','/') split_chain
from
  job job1
, job job2
, job job3
where
    job1.job_id(+) = job2.splitfrom
and job2.job_id(+) = job3.splitfrom

As for the merge chain, I think about using PL/SQL procefure, but not sure yet.
I would suggest posting your sample JOB table (without dates) and the desired simplified output from your last message to the "ANSI SQL" and "Oracle release 9i" groups, which are visited by more people knowing much more tricks.

 

[navrsalemile]

thanks nagornyi but I used sys_connect_by_path function as mentioned (i do not know if this function is present in pre 9i releases) to produce this query:

column change_history format a50
column participant format a12
select distinct DEPTH,
CHANGE_HISTORY,
substr( change_history, instr( change_history, '/', -1, 1 ) + 1 ) PARTICIPANT,
decode( DEPTH - 1, 0, null, EVENT ) EVENT
from (
select level DEPTH,
sys_connect_by_path( job_id, '/' ) CHANGE_HISTORY,
'SPLIT' EVENT
from job j
start with job_id = 'EEEE'
connect by prior split_from = job_id
union all
select level DEPTH,
sys_connect_by_path( job_id, '/' ) CHANGE_HISTORY,
'MERGE' EVENT
from job j
start with job_id = 'EEEE'
connect by prior job_id = merge_to
)

It gives this report (in this case it is only merging because there is no split link for this particular job_id :

DEPTH CHANGE_HISTORY PARTICIPANT EVENT
----- -------------------------------------------------- ------------ -----
1 /EEEE EEEE
2 /EEEE/CCCC CCCC MERGE
2 /EEEE/DDDD DDDD MERGE


Now I want to join this table or view (I am only interested in PARTICIPANT column here) with JOB_REPORT table. JOB_REPORT table has two columns:

JOB_ID same as in JOB table
JOB_NAME
YR1 number of people who worked on his job 5 yrs ago
YR2 number of people who worked on his job 4 yrs ago
YR3 number of people who worked on his job 3 yrs ago
YR4 number of people who worked on his job 2 yrs ago
YR5 number of people who worked on his job past year

I need to compute
SUM(YR1)
SUM(YR2)
SUM(YR3)
SUM(YR4)
SUM(YR5)

across all PARTICIPANTs, i.e. if the JOB_REPORT table has following records:

job_id job_name YR1 ...
=========================================
AAAA name1 6
BBBB name2 5
CCCC name3 3
DDDD name4 7
EEEE name5 10
FFFF name6 3


then the query based on join of PARTICIPANTs and JOB_REPORT table should look like:

job_id job_name YR1 ....
==========================================
AAAA name1 6
BBBB name2 5
CCCC name3 3+6 (add AAAA participant)
DDDD name4 7+5 (add BBBB participant)
EEEE name5 10+9+12(add CCCC and DDDD par-
ticipants)
FFFF name6 3+9 (add CCCC participant)

if the query is for all JOB_IDs

or just

job_id job_name YR1 ....
==========================================
EEEE name5 10+9+12

if the query is for specific JOB_ID (EEEE in this example).

So the question is how should I join PARTICIPANT inner view and JOB_REPORT table to produce above aggregation report?

thanks for answers,
mile