Full outer join in Ds

kailas · Post by **kailas** » Wed Nov 25, 2009 12:07 am

Hi ,

I have a requirement to get all the matching and not matching data . So I have used a full outer join to achive this . For the matching the data is showing fine but for non matching the key fields on which I am joining coming as NULL and the derived value is coming fine . But my requirement is that for all non matching also i should have the key fileds values so that we can know the derived value is for so and so key filed.
Out put of the target file is given below :

locn workcenter day-id wk_date Inontime outontime absentcount
0001 123456 09-11-19 09-11-21 15 12 0
0001 123456 09-11-20 09-11-21 16 18 0
NULL NULL NULL NULL 0 0 1

Above locn, workcenter, day_id and wk_date are key fields .
First I am using fullouter join to derive Inontime and outontime and then using one more full outer join to derive absent counts . In the same way i have many fields to derive using fullouter join .

In full outer join we have a left rec and right record . whether to take left record or right rec or to take both . If i take only left then in the output file for all the right records the key fields are coming as null .Which dont satisfy my requirement .

Any help will be appreciated.
Thanks

chulett · Post by **chulett** » Wed Nov 25, 2009 12:22 am

Your expectations are faulty. That's the way an outer join works, full or otherwise. Any data from the 'missing' side - key fields and all - will be null.

Take both.

kailas · Post by **kailas** » Wed Nov 25, 2009 12:30 am

[quote="chulett"]Your expectations are faulty. That's the way an outer join works, full or otherwise. Any data from the 'missing' side - key fields and all - will be null.

Take both. ...[/quote]

Thanks For the quick response.

As i have to use many full outer joins in the job how can i take each left and right rec and how to put that in the target file . If i have to use only two full outer joins then i can take both left and right rec and in the transformer take the null records as right and rest as left and then use a funnel and then put into a file . But i have many fileds to be derived so i will be using many full outer joins and the above will not help me in case of many outer joins . In the existing jobs lookups were used which was leftouter join but now the client want all the data so we planned to replace lookups with fullouter joins .

kamalshil · Post by **kamalshil** » Wed Nov 25, 2009 12:47 am

hi,
for full outer join.
get 1 dummy column in both source = any constant value i.e 1.

And in join stage do outer join key should be dummy column.
You will get full outer join result.

Thanks.

ray.wurlod · Post by **ray.wurlod** » Wed Nov 25, 2009 1:40 am

A full outer join is not the same thing as a Cartesian product.

Why not create an example with three rows on each input? Describe your expected output, and what you actually get.

Then describe what you're doing on the Mapping tab of the Output link, which is where I think you're missing picking up input key columns.

kailas · Post by **kailas** » Wed Nov 25, 2009 3:18 am

[quote="ray.wurlod"]A full outer join is not the same thing as a Cartesian product.

Why not create an example with three rows on each input? Describe your expected output, and what you actually get.

Then describe ...[/quote]

Ray ,

example is as given below :
Input 1
Locn WC Inontime
0069 101010 10
0069 102020 10

Input 2
Locn WC 0utontime
0069 101010 20
0029 106020 15

Input 3
Locn WC Abs
0019 101015 9
0020 106020 10

Expected o/P
Locn WC Inontime 0utontime Abs
0069 101010 10 20 0
0069 102020 10 0 0
0029 106020 0 15 0
0019 101015 0 0 9
0020 106020 0 0 10

CURRENT O/P

0069 101010 10 20 0
0069 102020 10 0 0
0029 106020 0 15 0
NULL NULL NULL NULL 9
NULL NULL NULL NULL 10

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Nov 25, 2009 3:51 am

What you need is a funnel and aggregator.

kailas · Post by **kailas** » Wed Nov 25, 2009 3:57 am

[quote="Sainath.Srinivasan"]What you need is a funnel and aggregator. ...[/quote]

Sainath ,

You are right . I have used a transformer where I am taking Null Keys as right and Rest Left and then using a funnel and its working fine also .
My problem is In the same join I have many fullouter joins and and i cannot use a tranformer and funnel after each join and if i use also its will be very difficult to take all right record and left record from each joiner .

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Nov 25, 2009 4:02 am

What I am trying to say is, if your inputs are as mentioned in your post just above mine, you do not need a join.