complex requirement, need urgent help...

UDA
Enthusiast

complex requirement, need urgent help...

Hi guys!

Below is a complex requirement which ideally, I have to solve by tomorrow. Basically, data is about job dependency information which is normalized and requirement is to somehow denormalize it.

Can you help me write the SQL for this?
I'm thinking that I should be using ROW_NUMBER() for generating the sequence in one scenario below.
Please read on, and inputs is very much appreciated.

I have attached the data in MS Excel file since below data is not aligned properly.

Input:
JOB_NAME SOURCE_TABLE TARGET_TABLE BKEY_JOB BMAP_JOB
BKEY1 SOURCE1 BKEYTGT1 --> #2
BKEY1 SOURCE2 BKEYTGT1 --> #2
BKEY2 SOURCE2 BKEYTGT2
BMAP1 SOURCE1 BMAPTGT1
BMAP2 SOURCE1 BMAPTGT1
JOB1 SOURCE1 TARGET1 BKEY1 BMAP1 ---> #1
JOB2 SOURCE1 TARGET1 BKEY1 BMAP2 ---> #3
JOB3 SOURCE2 TARGET1 BKEY2 BMAP2
JOB4 SOURCE1 TARGET2

#1 JOB1's source is SOURCE1. It populates TARGET1 and is using BKEY1 and BMAP1 job
#2 BKEY1 itself is a job. It is getting data from both SOURCE1 and SOURCE2. It populates BKEYTGT1.
#3 JOB2 is also populating to TARGET1. It is using BKEY1 and BMAP2 job
#4 BMAP1 and BMAP2 both have same source and target tables. This is possible also for BKEYs.
#5 Basically, PK here is JOB_NAME and SOURCE_TABLE

Output:
PK_JOB_NAME JOB_NAME DEPENDENCY TARGET_TABLE
BKEY1 BKEY1 SOURCE1 BKEYTGT1
BKEY1 SOURCE2
BKEY2 BKEY2 SOURCE2 BKEYTGT2
BMAP1 BMAP1 SOURCE1 BMAPTGT1
BMAP2 BMAP2 SOURCE1 BMAPTGT1
JOB1 JOB1 SOURCE1 TARGET1
JOB1 BKEY1
JOB1 BMAP1
JOB2 JOB2 SOURCE1 TARGET1 --> #2
JOB2 BKEY1
JOB2 BMAP2
JOB2 JOB1 --> this row has to be generated since it also populates TARGET1
JOB3 JOB3 SOURCE2 TARGET1 --> #3
JOB3 BKEY2
JOB3 BMAP2
JOB3 JOB2 --> this row has to be generated since it also populates TARGET1
JOB4 JOB4 SOURCE1 TARGET2

#1 For a specific PK_JOB_NAME, only DEPENDENCY can have multiple values (in different rows).
JOB_NAME and TARGET_TABLE should not repeat
#2 JOB1 and JOB2 are both populating to TARGET1.
As per requirement, only one job can populate to a target table.
Hence, we should generate a new row for JOB2, where DEPENDENCY = "JOB1"
#3 Same case here, JOB3 is also populating to TARGET1.
Hence, we should generate a new row for JOB3, where DEPENDENCY = "JOB2", which is the previous job only.
To know what is the previous job, sequence is ORDER BY TARGET_TABLE, SOURCE_TABLE, PK_JOB_NAME
1 REPLY
Enthusiast

Re: complex requirement, need urgent help...

Sorry for late post, please disregard, have already done this..