FULL outer join
Moderators: chulett, rschirm, roy
FULL outer join
How to perform a full outer join in Datastage Server jobs.
I am having two tables. One table is populated from flat files. One table is ERP(SAP) table. The ERP table is having keycodes and descriptions. The normal table is having some extra keycodes. I want to perform a full outer join.
How to accomplish this
I am having two tables. One table is populated from flat files. One table is ERP(SAP) table. The ERP table is having keycodes and descriptions. The normal table is having some extra keycodes. I want to perform a full outer join.
How to accomplish this
Pradeep Kumar
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
Re: FULL outer join
What about using a Link Merge stage? Of course not directly...but if you put a little thought in it the link merger state should help you out perfectly.
Or left outer joins doing two times,
Something like
if you don't want to use the link merge stage.
Something like
Code: Select all
Table
|
SrcFile ---->Trx----->Tgt
Srcfile
|
Table------>Trx------->Tgt
Success consists of getting up just one more time than you fall.
-
- Participant
- Posts: 83
- Joined: Sat Oct 28, 2006 6:25 am
-
- Premium Member
- Posts: 81
- Joined: Mon Nov 21, 2005 4:17 am
- Location: Sydney, Australia
- Contact:
FULL Outer Join
HI,
Take the two inputs into two sequential files and try Complete Set.
Hope this could serve the purpose.
Regards,
Suresh N
Take the two inputs into two sequential files and try Complete Set.
Hope this could serve the purpose.
Regards,
Suresh N
SURESH NARASIMHA
I'm pretty sure Suresh is saying to use the sequential files in the merge stage, not as a referential link to a transformer.pradkumar wrote:I think a transformer stage cannot get reference input from sequential file
The merge stage does not have any input links per se. You specify the two sequential files you wish to use and have output links only.
Hey Using the merge stage you can perform as many as eight type of joins on files. Read below:
Type of Join Operation Description
Pure Inner Join A AND B Merges only those rows with the same key values in both input files.
Complete Set A OR B Merges all rows from both files.
Right and Left Only A NOR B Merges all rows from both files except those rows with the same key values.
Left Outer Join A Merges all rows from the first file (A) with rows from the second file (B) with the same key value.
Right Outer Join B Merges all rows from the second file (B) with rows from the first file (A) with the same key value.
Left Only A NOT B Merges all rows from the first file except rows with the same key value in the second file (B).
Right Only B NOT A Merges all rows from the second file except rows with the same key value in the first file (A).
If, this is not what you are lookig for or i have answered incorrectly then sorry:)
Type of Join Operation Description
Pure Inner Join A AND B Merges only those rows with the same key values in both input files.
Complete Set A OR B Merges all rows from both files.
Right and Left Only A NOR B Merges all rows from both files except those rows with the same key values.
Left Outer Join A Merges all rows from the first file (A) with rows from the second file (B) with the same key value.
Right Outer Join B Merges all rows from the second file (B) with rows from the first file (A) with the same key value.
Left Only A NOT B Merges all rows from the first file except rows with the same key value in the second file (B).
Right Only B NOT A Merges all rows from the second file except rows with the same key value in the first file (A).
If, this is not what you are lookig for or i have answered incorrectly then sorry:)
I was able to accomplish it. I did not trim of one key column. Now i am having one basic doubt. My Target is a sequential file. I developed the following in a single job and executed it. How do I see the output. One is reading x rows and other is reading y rows.
How do I see the output of outer join. Do I need to buid two separate jobs?
Table
|
SrcFile ---->Trx----->Tgt
Srcfile
|
Table------>Trx------->Tgt
How do I see the output of outer join. Do I need to buid two separate jobs?
Table
|
SrcFile ---->Trx----->Tgt
Srcfile
|
Table------>Trx------->Tgt
Pradeep Kumar
-
- Participant
- Posts: 70
- Joined: Thu Nov 09, 2006 2:14 am