Hi..
How to do left outer join and right outer join in DataStage 7.5.2
How to do left outer join and right outer join in DS 7.5.2
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 11
- Joined: Thu Sep 13, 2007 12:56 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Welcome aboard.
Assuming this is a parallel job, since you posted in the parallel forum, you use a Join stage.
If it is a server job you can only do a left outer join, using a Transformer that has a reference input link. However, it is easily provable that reversing the sense of left and right allows a right outer join to be executed as a left outer join.
In both cases, it is probably better to perform the join in the source database, where it can be aided by indexes on the join key columns.
Assuming this is a parallel job, since you posted in the parallel forum, you use a Join stage.
If it is a server job you can only do a left outer join, using a Transformer that has a reference input link. However, it is easily provable that reversing the sense of left and right allows a right outer join to be executed as a left outer join.
In both cases, it is probably better to perform the join in the source database, where it can be aided by indexes on the join 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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The natural behaviour of the Transformer stage is to perform a left outer join; NULL is returned from the "right" (reference) input if the lookup fails.girish.rupkumar (by private email) wrote:Hi..
Thank you very much for your solution.
But am confused, how can we do left outer join using transformer stage??
Do we need to check for Isnull condition in constraint division??
You only check using IsNull() if you are seeking to perform an inner join.
Think about it.
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.
Left/Outer Join
..ray.wurlod wrote:girish.rupkumar (by private email) wrote:Hi..
Thank you very much for your solution.
But am confused, how can we do left outer join using transformer stage??
Do we need to check for Isnull c ...
Hi Friends ,
With reference to your concern , For left/Right Outer Join is not possible through Transformation Stage because this stage is actually used for transfomation the Data flow with defined particular business logic as per our requirement for end user . But Join stage is the only stage through you can join two datasets/DB table on particular key field only .
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: