FULL OUTER JOIN

[nynali]

Hi,
I have two files X and Y and my requirement is to join them and I want two outputs from the join.
One will have all the rows from file X.
The other will have rows from file Y which did not get a hit with X.

As of now I am doing a join and then passing them to a transformer and then trying to get the two outputs.So please help me how to put constraints to get the exact outputs as mentioned above in the transformer.

[gateleys]

I would do it in 2 jobs.
JOB1: File1 is primary input and File2 is reference. The output link should have no constraint. This performs a File1 LEFT OUTER JOIN File2.

JOB2: Toggle the files. So, File2 is primary and File1 (hashed) is reference. In this case, the output link constraint is IsNull(RefLink.KeyCol).

gateleys

[ray.wurlod]

I would use one job, with the Join stage performing a left outer join (with X as the left input). Downstream of the Join stage a Filter or Transformer stage would segregate the input into two streams, one receiving all rows (unconstrained), the other receiving only rows where the key column from Y is null.

[rcanaran]

I have a similar situation. I have 3 files. (A, B an C). A is th driver and I do two outer joins in the join stage. The JOIN stage requires that the join columns be identically named. From the JOIN STAGE, STAG tab, PROPERTIES tab,PROPERTIESIn the columns available for target for the key , the help is

"Type: Input Column
Name of input column you want to join on. Columns with the same name must appear in both input data sets and have compatible data types."

In the OUTPUT tab there is only one source column for the join key and I cannot edit the derivation, only drag source to target.
the following transformer stage can only access the output link from the join, so only has ONE key and ONE streamed input. Can it reference the INPUT links of the JOIN stage?

[ray.wurlod]

Please post your question in a new thread. It is NOT the same, and only vaguely similar, to the question asked - and already resolved - on this thread. There is an answer, but I'll not post it here.