set default value on look up failure

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
perspicax
Premium Member
Premium Member
Posts: 26
Joined: Thu Dec 07, 2017 3:11 pm
Location: USA

set default value on look up failure

Post by perspicax »

I have set the lookup stage conditions to 'continue' on lookup failure. These lookups are surrogate key lookup. It's defaulting to 0 for these values. Is there a way to default to a different value, let's say -1?

Thanks
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Not directly, but you can set the output column to nullable then check for null in a subsequent Transformer stage then set the value to whatever you want to.
Choose a job you love, and you will never have to work a day in your life. - Confucius
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

if dealing with 'very' large volumes and few failed lookups you may find it more efficient to reject them and only transform the reject link, then funnel back. But generally, the above is the best way. Its more stages to reject but less actual work, as far as I can tell.
perspicax
Premium Member
Premium Member
Posts: 26
Joined: Thu Dec 07, 2017 3:11 pm
Location: USA

Post by perspicax »

It works! I was using isnull vs ISNULL to handle and I think it is case sensitive.

In the hash file look up there is a function called NOTFOUND. Is this not relevant in parallel jobs?

Thanks!
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

It's not relevant in parallel jobs because you should use the reject link functionality instead.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply