DSXchange

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

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.

Hmm...Little more ideas

Or left outer joins doing two times,

Something like
if you don't want to use the link merge stage.

How to accomplish this task in MERGE STAGE

I tried using the previous solution

My transformer stage is getting primary input as well as refernce input from tables. It is not reading anything from reference table. The job is not giving any errors. What might be the reason?

The lookup must be failing i.e no rows are retrieved.

pradkumar wrote:I tried using the previous solution

My transformer stage is getting primary input as well as refernce input from tables. It is not reading anything from reference table. The job is not giving any errors. What might be the reason?

How to overcome this lookup..Any suggestions plz

HI,

Take the two inputs into two sequential files and try Complete Set.

Hope this could serve the purpose.

Regards,
Suresh N

I think a transformer stage cannot get reference input from sequential file

pradkumar wrote:I think a transformer stage cannot get reference input from sequential file

I'm pretty sure Suresh is saying to use the sequential files in the merge stage, not as a referential link to a transformer.

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:)

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

Thanks for all the suggestions

pradkumar wrote:How to overcome this lookup..Any suggestions plz

Hi PradKumar,
I think V can use HashFile as a refference lookup and v can match Both primary key links(from hash file key and source file key) in transformer stage.
Regard`s
Shrini