Lookup - Selective Records; Other Continue

vijayrc · Post by **vijayrc** » Thu Feb 22, 2007 3:46 pm

Hello,
I have a scenario, wherein I have a
Dataset==>Transformer==>Lookup==>Dataset

There's one set of records [Input Field1=00]in Input wherein it wouldn't find a Lookup in the Reference, and it should just pass the entire Input record to the Output. For the rest of the records [Input Field1 <> 00], Do a lookup and derive some values..

How do I selectively put a Constraint for this case.
Thanks
Vijay

DSguru2B · Post by **DSguru2B** » Thu Feb 22, 2007 3:55 pm

You can have a reject link coming out of the Lookup Stage that will carry records which will fail the lookup. Send both the inputs (lookup successful and lookup failure which is the reject link) to a funnel stage. The output of funnel stage can go to your final dataset.

vijayrc · Post by **vijayrc** » Thu Feb 22, 2007 3:57 pm

DSguru2B wrote:You can have a reject link coming out of the Lookup Stage that will carry records which will fail the lookup. Send both the inputs (lookup successful and lookup failure which is the reject link) to a funnel stage. The output of funnel stage can go to your final dataset.

Thanks for the suggestion. I had that in mind, but I want to see if there's a way w/o using any additional stage to accomplish this

DSguru2B · Post by **DSguru2B** » Thu Feb 22, 2007 3:58 pm

I hardly doubt it as you cannot constraint any input inside the lookup stage. You will have to either seperate it in the transformer and then collect it later or go with the other method I advised.

ray.wurlod · Post by **ray.wurlod** » Thu Feb 22, 2007 5:51 pm

The Lookup stage has its own "Constraints" area - click on the second tool from the left in its toolbar.

vijayrc · Post by **vijayrc** » Thu Feb 22, 2007 6:01 pm

ray.wurlod wrote:The Lookup stage has its own "Constraints" area - click on the second tool from the left in its toolbar. ...

Ray,
The Constraints list the reference link and the derivation bar for the constraint doesn't allow me to select any Input Column. Thanks again for all the inputs.

vmcburney · Post by **vmcburney** » Thu Feb 22, 2007 6:52 pm

If you have the right version of DataStage, I think it's the latest Unix/Linux version but not the Windows version, you can do a conditional lookup using the parallel lookup stage. The constraints form lets you enter a condition on whether the lookup should be performed for each row using values in the input link.

This lets you skip the lookup for rows that meet the criteria.

ray.wurlod · Post by **ray.wurlod** » Thu Feb 22, 2007 7:15 pm

Even version 7.5x2 (Windows) permits conditional lookups.