Do not want null value records to be rejected by Lookup Stg

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
nikita.bandekar
Participant
Posts: 6
Joined: Mon May 06, 2019 6:42 am

Do not want null value records to be rejected by Lookup Stg

Post by nikita.bandekar »

I have a lookup stage which has input records with null values. Basically I want the lookup stage to Reject records when there are unmatched records but continue when there are null values on the key.
My input columns and receiving columns are nullable and Lookup Failure is set to 'Reject'

Not sure where i am going wrong.
Thanks in advance.

Regards,
Nikita
nikita bandekar
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Take this with a grain of salt as it's been a long time but I think you'll need to stop rejecting any records by the Lookup stage. After the lookup, query the results and "reject" them yourself by looking for NULL values in the returned keys and compare that to the submitted keys. Decide based on the source values.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Use a conditional lookup with an IsNotNull() condition on the lookup key in the stream input, and mark the Condition Failed rule Continue.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

A "conditional lookup"? All that comes to mind is something from years ago, a specific stage which was only available in the mainframe version of the product. Has that been merged into the core product?

Okay, a quick search seems to confirm my memory. It is only listed under the Mainframe Job Stages topic online and for some reason only up through the 9.x version of the product. Odd that trying to switch the version to 11.x give me an error about no matching topic. :?

Still seems to me that you will need to provide the conditional logic...
-craig

"You can never have too many knives" -- Logan Nine Fingers
nikita.bandekar
Participant
Posts: 6
Joined: Mon May 06, 2019 6:42 am

Post by nikita.bandekar »

ray.wurlod wrote:Use a conditional lookup with an IsNotNull() condition on the lookup key in the stream input, and mark the Condition Failed rule Continue. ...
This worked like a charm!! :D
Thank you guys for your help :)
nikita bandekar
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

So... it is no longer relegated to only existing in the Mainframe product? Seems I need to go scrub through the online help again. :?
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply