Capturing rejected records

[dsuser08]

Hi

Is it possible to capture rejected records using join component.I will be performing Fullouter,left outer,right outer and inner join.I need to capture rejeted records in sequential file.

Can I use merge stage for the above.In Merge ,We have Reject mode:output.It says pass rejected records down reject link.

does it mean
Input Processing Output
sequentialFile1 Merge sequentialFile3(Matched records)
sequentialFile2 sequentialFile4(UnMatched records)
or

Input Processing Output
sequentialFile1 Merge sequentialFile3(Matched records)
sequentialFile2
From sequentialFile3(Matched records) ,I have to give a link to sequentialFile4 which will capture UnMatched records
Thanks

[ray.wurlod]

Welcome aboard. Just as in SQL there is no such thing as a rejected record when a join is performed. A row is output from the join if the join criteria are satisfied. Nothing else happens.
This is why the Join stage does not have support for a Reject output link.

[Maveric]

Join does not support reject link. Use a lookup stage if the reference data volume is low or merge stage.

[dsuser08]

Thanks so much for your reply.In Merge ,We have Reject mode:output.It says pass rejected records down reject link.

does it mean
Input Processing Output
sequentialFile1 Merge sequentialFile3(Matched records)
sequentialFile2 sequentialFile4(UnMatched records)
or

Input Processing Output
sequentialFile1 Merge sequentialFile3(Matched records)
sequentialFile2
From sequentialFile3(Matched records) ,I have to give a link to sequentialFile4 which will capture UnMatched records

Please explain

[gnreddy]

Hi,

The best way to capture rejected data is LookUp Stage.
U can send matched records to one table and rejected records to one table.

Regards,

[dspxlearn]

gnreddy,

What if you want ONLY the rejected records(unmatched) records from the lookup stage and the matched records are not be be captured anywhere?
[/code]

[dspxlearn]

gnreddy,

What if you want ONLY the rejected records(unmatched) records from the lookup stage and the matched records are not be be captured anywhere?
[/code]

[ray.wurlod]

What if you want ONLY the rejected records(unmatched) records from the lookup stage and the matched records are not be be captured anywhere?

Send the matched records into a Copy stage with no output link. It will eat them happily.

[snt_ds]

Thanks ray,

I did the same when i was working on this kind of stuff yesterday. But just wanted to confirm :D . Can we use an external target stage and direct the output to the Unix blackhole (>> Null)?

[ray.wurlod]

Yes, but the UNIX "black hole" is /dev/null, not NULL.

In Windows it's .\NUL (there is one in every folder). Hmm, maybe that tells us something about the O/S!

I still prefer the Copy stage. Add an annotation and/or a description in the Copy stage to alert the next developer to what you intend by having no output link.

[dspxlearn]

Thanks Ray!!
This post for really informative. :D
Dsuser,
Time to mark it as Resolved if you are allset.

[dsuser08]

If I have to use Full-outer or Right outer or Left outer and capture rejected records,Which component should I use.Please help.

[ag_ram]

There is no rejected records in Join. You can't get the matched and unmatched records in two separate links using join stage. Instead You can capture the nonmatching records from JOIN by using a transformer satge or a filter stage after the join stage:
Full Outer Join: Give a condition on the transformer or filter stage to filter out the records which is having NULL on the key column from left record or right record
Right Outer Join: Give a condition in the transformer or filter stage to filter out the left records having NULL.
and vice versa for Left Outer join

[ray.wurlod]

If I have to use Full-outer or Right outer or Left outer and capture rejected records,Which component should I use.Please help.

What do you mean by "rejected"? If you do a join in SQL, where are the rejected rows? DataStage is no different.