DSXchange

Hey everyone,
I wanted to ask a simple question, does anyone know where I would set the default value for a failed lookup that is set to continue? In a numeric lookup the default we get is a 0, we're hoping we can just change the default returned value as opposed to adding additional logic to look for 0s and change them. I'm open to any suggestions. Thanks in advance!!

So, actually for a Parallel job and not a Server job as marked yes?

Correct, I put this in general as I was unsure if the configuration options to do this for a lookup would be parallel specific. My apologies.

No worries, let me scootch you over to the correct forum...

I am not sure that you can change the default value. One option is that you could make the column coming from the reference link as nullable. In a subsequent transformer stage you can build some logic to replace the nulls with the default value of your choice. Alternatively, you can send the lookup failures to a reject link, and add specific values for the column in question and then funnel back to the main stream.

It all depends upon what is the overall goal.

Hope this helps,
Raj

dsguy1247,
I kind of figured this was the route we'd have to go. I could have sworn setting a default lookup failure value was an option somewhere from the courses I took, but I wonder thats for string lookups only? In either case I believe you're correct and we'll be opting to send failed lookups to rejects and we'll set the value from a subsequent transformer from there.

The Modify stage lookup functions allow for a default value to be specified. Perhaps this is what you encountered during your training.

Hmm that might be it, here is a pretty interesting article about that stage.
https://it.toolbox.com/blogs/vincentmcb ... ter-110807

It sounds like I could use this stage to handle those lookup values though. Thanks guys!

Jackson,

When I used to teach Datastage for IBM, I used to teach that modify stage is much less expensive than the transformer stage, hence when performance is critical, it is better to use modify stage. Then, during one of the debugging sessions with IBM tech support for a client, the support guy said that the in the latest versions, the CPU utilization was no longer a huge differentiator. I did not question him.

However, the drag and drop features of most of the stages, where you can drag the input columns to output, is not available in the modify stage, which makes it a little painful and prone to silly typo errors.

On the original post, I believe you have a work around, but I just wanted to share my two cents on my "favorite" modify stage BTW, the Vincent article was great and it makes a lot of sense. In fact, any article that quotes Ray Wurlod is just awesome.

Thanks,
Raj

dsguy1247 wrote:the support guy said that the in the latest versions, the CPU utilization was no longer a huge differentiator. I did not question him.

Ray has made the same observation in the past as well. I also did not question him.