Multiple source systems, need only one non null record

khushbu6 · Post by **khushbu6** » Wed Feb 22, 2017 11:02 pm

Hi,

I have a scenario where I have records from multiple source systems like suppose there are 4 src system s1 s2 s3 s4.. I have policy number ,policy details ,name and general details from all the 4.

I need output where I have to get only one record for one policy number.I can have a requirement of first not null field then in that case , if there is null field in s1 and not null in s2, in that case it must be record from s2 that must go to output , I can have requirement to have last not null field, in that case if s4 is null, the record with s3 must go to output.

How to implement that?

ray.wurlod · Post by **ray.wurlod** » Thu Feb 23, 2017 12:33 am

Welcome aboard!

If all four sources are in the same database, effect a four way join and use COALESCE function in your SELECT query.

Otherwise (for example if you have heterogeneous data sources), effect the join in DataStage then use a downstream Transformer stage with a three-level If..Then..Else construct to implement your test.

Code: Select all

If Not(IsNull(s1.field)) Then s1.field Else If Not(IsNull(s2.field)) Then s2.field Else If Not(IsNull(s3.field)) Then s3.field Else s4.field

khushbu6 · Post by **khushbu6** » Thu Feb 23, 2017 4:54 pm

I tried but it wouldnt work as the field names are same .
There are basically imagine 4 data sets present, each with exactly same schema.so they have same fields namely policy number, policy location in all 4 datasets.Policy location may be null in 1st dataset and in that case I need to pick policy location from 2nd dataset.

chulett · Post by **chulett** » Thu Feb 23, 2017 6:32 pm

So... you've said "dataset" now... does that mean this is actually a Parallel job?

khushbu6 · Post by **khushbu6** » Thu Feb 23, 2017 6:55 pm

Really sorry.. Dont know how it selected server jobs.. it is yes paralled job..

chulett · Post by **chulett** » Thu Feb 23, 2017 7:55 pm

No worries, let me get you moved into the correct forum.

ray.wurlod · Post by **ray.wurlod** » Thu Feb 23, 2017 9:22 pm

Use a Funnel stage to combine the four outputs, and a Transformer stage to loop through each group of four to transfer the first non-null record (use stage variables for each group to determine whether or not it has been output).