How to do left outer join and right outer join in DS 7.5.2

[girish.rupkumar]

Hi..

How to do left outer join and right outer join in DataStage 7.5.2

[ray.wurlod]

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.

[ray.wurlod]

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??

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.

You only check using IsNull() if you are seeking to perform an inner join.

Think about it.

[Sourav]

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 .

[ray.wurlod]

The job type in this thread is marked as "server". In which Transformer stage can and does perform a left outer join.