Full outer join in Ds
Moderators: chulett, rschirm, roy
Full outer join in Ds
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
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
[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 .
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 .
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
[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
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
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
[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 ,
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 .
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom