navrsale wrote:
> In my application there are two given tables:
>
> 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
>
> create table job (
> job_id char(4) NOT NULL,
> open_dte date not null,
> close_dte date,
> split_from char(4),
> merge_to char(4),
> PRIMARY KEY (job_id),
> FOREIGN KEY (split_from) REFERENCES job(job_id),
> FOREIGN KEY (merge_to) REFERENCES job(job_id)
> )
> /
> INSERT INTO job VALUES( 'AAAA', to_date('01/01/90', 'MM/DD/RR'),
> to_date('01/01/00', 'MM/DD/YY'), null, null )
> /
> INSERT INTO job VALUES( 'BBBB', to_date('01/01/80', 'MM/DD/RR'),
> to_date('01/01/00', 'MM/DD/YY'), null, null )
> /
> INSERT INTO job VALUES( 'CCCC', to_date('01/01/00', 'MM/DD/RR'),
> to_date('01/01/01', 'MM/DD/YY'), 'AAAA', 'EEEE' )
> /
> INSERT INTO job VALUES( 'DDDD', to_date('01/01/00', 'MM/DD/RR'),
> to_date('01/01/01', 'MM/DD/YY'), 'BBBB', 'EEEE' )
> /
> INSERT INTO job VALUES( 'EEEE', to_date('01/01/01', 'MM/DD/RR'),
null,
> null,
> null )
> /
> INSERT INTO job VALUES( 'FFFF', to_date('01/01/01', 'MM/DD/RR'),
null,
> 'CCCC',
> null )
> /
> commit
> /
>
>
> and JOB_REPORT table:
>
> job_id job_name YR1 YR2 YR3 YR4 YR5
> ======================================================
> AAAA name1 6 5 4 3 2
> BBBB name2 5 4 3 2 1
> CCCC name3 3 2 1 2 3
> DDDD name4 7 6 5 4 3
> EEEE name5 10 9 8 7 6
> FFFF name6 3 4 5 6 7
>
>
> create table job_report (
> job_id char(4) NOT NULL,
> job_name char(5) not null,
> yr1 integer,
> yr2 integer,
> yr3 integer,
> yr4 integer,
> yr5 integer
> PRIMARY KEY (job_id)
> )
> /
>
> INSERT INTO job_report VALUES( 'AAAA', 'name1', 6,5,4,3,2 )
> /
> INSERT INTO job_report VALUES( 'BBBB', 'name2', 5,4,3,2,1 )
> /
> INSERT INTO job_report VALUES( 'CCCC', 'name3', 3,2,1,2,3 )
> /
> INSERT INTO job_report VALUES( 'DDDD', 'name4', 7,6,5,4,3 )
> /
> INSERT INTO job_report VALUES( 'EEEE', 'name5', 10,9,8,7,6 )
> /
> INSERT INTO job_report VALUES( 'FFFF', 'name6', 3,4,5,6,7 )
> /
> commit
> /
>
>
> JOB table describes different jobs and their history (some jobs are
> split into another job and some jobs are merged into a new job). For
> example this hierarchical query describes history inside JOB table:
>
> column change_history format a50
> column participant format a12
> set pagesize 66
> accept p_job_id prompt 'enter job id, 0 for all: '
>
> 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
> start with DECODE( '&p_job_id', '0', '1', job_id ) = decode(
> '&p_job_id', '0', '1', '&p_job_id')
> connect by prior split_from = job_id
> union all
> select level DEPTH,
> sys_connect_by_path( job_id, '/' ) CHANGE_HISTORY,
> 'MERGE' EVENT
> from job
> start with DECODE( '&p_job_id', '0', '1', job_id ) = decode(
> '&p_job_id', '0', '1', '&p_job_id' )
> connect by prior job_id = merge_to
> )
> order by 2;
>
> which gives this result:
>
> DEPTH CHANGE_HISTORY PARTICIPANT EVENT
> --------- ----------------------- ------------ -----
> 1 /AAAA AAAA
> 1 /BBBB BBBB
> 1 /CCCC CCCC
> 2 /CCCC/AAAA AAAA SPLIT
> 1 /DDDD DDDD
> 2 /DDDD/BBBB BBBB SPLIT
> 1 /EEEE EEEE
> 2 /EEEE/CCCC CCCC MERGE
> 2 /EEEE/DDDD DDDD MERGE
> 1 /FFFF FFFF
> 2 /FFFF/CCCC CCCC SPLIT
> 3 /FFFF/CCCC/AAAA AAAA SPLIT
>
>
> So here is the problem: a query must be created which combines above
> "history query" with table JOB_REPORT to produce following result:
>
> job_id job_name YR1 ....
> ==========================================
> AAAA name1 6
> BBBB name2 5
> CCCC name3 9 (*
> DDDD name4 12 (**
> EEEE name5 31 (***
> FFFF name6 12 (****
>
> if the query is for all JOB_IDs
>
> or just
>
> job_id job_name YR1 ....
> ==========================================
> EEEE name5 31
>
> if the query is for specific JOB_ID (EEEE in this example). Query
must
> show data for every YR (YR1, YR2, YR3, YR4, YR5)
>
> remark (* : 9 is sum of 3 and 6 where 3 is the number of people who
> worked on job CCCC, and where 6 is the number of people who worked on
> job AAAA from which job CCCC was split. In this example I only showed
> column YR1 but the same logic is applicable for columns YR2, YR3,YR4
> and YR5 as well
>
> remark (**: 12 is the sum of 7 and 5 where 7 is the number of people
> who worked on job DDDD, and where 5 is the number of people who
worked
> on job BBBB from which job DDDD was split.
>
> remark (***: 31 is the sum 10+9+12 where 10 is the number of people
who
> worked on job EEEE and 9 is explained in remark (* and 12 is
explained
> in remark (**. I.e. CCCC and DDDD were merged into EEEE
>
> remark (****: 12 is the sum 3+9 where 3 is the number of people who
> worked on job FFFF and 9 is explained in remark (*. I.e. FFFF was
split
> from job CCCC.
>
> Basically, in this example data, job CCCC was created by splitting
from
> job AAAA, and job DDDD by splitting from job BBBB. CCCC and DDDD were
> then merged into EEEE, while at the same time job FFFF was split from
> CCCC. Query must process each row in JOB_REPORT table and aggregate
the
> number of employees when these numbers represent the "history" of
> particular JOB_ID to correct existing value in JOB_REPORT table (to
> "prorate" the number of employees in JOB_REPORT table based on the
> history of given JOB_ID represented inside JOB table).
In line with the other replies, I can afford only 5 minutes onto this
problem. The key to the solution might be the idea that there aren't
really 2 hierarchies in your case. (And generally, I've never seen 2
hierarchies at once). What you have is directed acyclic graph, where
each node is a job, and each directed job connects 2 adjacent nodes.
This generalization covers merges and splits nicely. Therefore, I
suggest reorganizing your schema (by means of physical redesign, or
merely by creating views) into 2 tables: job_nodes and job_links.
Received on Wed Feb 23 2005 - 15:19:45 CST
