Lookup stage in DS

Palci · Post by **Palci** » Tue Nov 22, 2011 1:40 am

In Lookup stage in Datastage, I want to abort my job if I get multiple matching records from my lookup table. How do I do this in the lookup stage??

ShaneMuir · Post by **ShaneMuir** » Tue Nov 22, 2011 3:06 am

I am not sure that it can be done in the lookup stage alone.

There are ways of triggering an abort from a sequence (as the lookup stage can return a warning if there are multiple records returned).

Reading between the lines - I assume that there is some sort of duplication issue from the lookup? Wouldn't it be better to check for and remove duplicates first, before the lookup?

meet_deb85 · Post by **meet_deb85** » Tue Nov 22, 2011 6:02 am

Why not check for duplicates before the reference link data comes to lookup and abort the job

Palci · Post by **Palci** » Tue Nov 22, 2011 11:49 pm

Well I don't want to remove duplicates nor I want unique records from my lookup. All I want is to abort the job if lookup returns multiple match. I guess this can be done in 8x versions of DS. Has anybody implemented thsi in there jobs??

ShaneMuir · Post by **ShaneMuir** » Wed Nov 23, 2011 3:37 am

As far as I am aware a lookup stage will only cause an abort if you set the lookup Failure to 'Fail' on the lookup stage constraints and that lookup failure happens.

A warning will be issued if there are multiple rows returned from a link and you haven't expressly allowed multiple rows to be return from that link. If there is a warning issued then you can definitely cause a controlling sequence to stop, but only after the job has completed.

I don't think that the warning issued can be changed to a fatal error, but I could be wrong.

qt_ky · Post by **qt_ky** » Mon Nov 28, 2011 11:08 am

You cannot do it in the Lookup stage alone but you can abort the job itself if you want to by adding some logic.

Before the Lookup stage, assign a unique ID to each row by using something like @INROWNUM in a Transformer stage (modify as needed if you're running multiple nodes, but probably not necessary).

Set the Lookup stage to allow multiple rows returned from the lookup link.

After the Lookup stage, add a Transformer stage with two stage variables: 1. a counter and 2. previous unique row ID. The counter derivation can be set like: If incoming unique row ID = previous unique row ID Then counter + 1 Else 1.

Add an extra output link to the Transformer stage with a constraint of counter > 1 and set Abort After Rows to 1. This setting will force the job to abort based on the above logic (if Lookup returns multiple rows).