Hi,
I want to simulate the following logic in 1 transformer:
If data is found after hash1 lookup then
move data from hash1 to output
Else
lookup hash file 2
If data is found in hash2
move data from hash2 to output
Else
move 'not found' to output
End If
If data is single column then using stage variables you can achieve it (using a single target link).
If it's not then you would need to create three links from the transformer writing to the same target.
First link would be used if NOT(lkp1.NOTFOUND) is ture.
Second link would be used if lkp1.NOTFOUND and NOT(lkp2.NOTFOUND) is true.
Third would be used if lkp1.NOTFOUND and lkp2.NOTFOUND is true.
Hope this will help you.
Success consists of getting up just one more time than you fall.
You could consider using transformers with link collectors to simulate a conditional lookup effect.
Do the first lookup, and based on the result send to two different output links. The first output link acts as if the lookup was found, the second is the lookup not found. Send the first output link to a link collector. From the second output link, go to another transformer and do the second lookup. Send this output to the link collector as well. Now you have the conditional lookup.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Or just have it do both lookups and check them in the proper order. No need for stage variables or extra links, really.
For something as simple as this I don't see a need to go to the trouble of Link Collectors and all that, but it should be kept in mind for the future. Your post has almost the exact logic you would leverage, you just need to move past the perceived need for the lookup to be 'conditional'. The 'condition' is enforced by the order you check each link for success.
Well, unless there is a tremendous amount of overhead involved in on or both of the lookups and they really affect the performance of the job - then consider the methodology Ken mentions.
-craig
"You can never have too many knives" -- Logan Nine Fingers
Or just have it do both lookups and check them in the proper order.
The 'condition' is enforced by the order you check each link for success.
Hi Chulett,
ekareem wants to get the data from the two different Hashed lookup files (not from the source). Do you mean that its possible through only one output link? I am not able to understand it. Can you please explain, how would you achieve that?
Thanks
Success consists of getting up just one more time than you fall.
The original subject, "Condition lookup", means that the second lookup is only attempted should the first lookup meet a condition. All lookups in a single tranformer are always attempted. Constraints have no impact whether a reference SQL is fired or a hash reference read is made.
The only method to do a conditional lookup is thru separation of conditions and use of transformers with constraints to divide the rows and send them on for further processing and lookup, with the rows meeting earlier conditions bypassing that subsequent logic. The link collectors are the method for bringing the rows back together.
It's nasty looking, but the only method to make a reference a condition. Personally, I like the cleaner design approach.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
kcbland wrote:The original subject, "Condition lookup", means that the second lookup is only attempted should the first lookup meet a condition. All lookups in a single tranformer are always attempted. Constraints have no impact whether a reference SQL is fired or a hash reference read is made.
Never said that they did. And as you noted, there is no such thing (unless you go 390). Just trying to point out that in this case, as in most cases when someone says they 'need a conditional lookup'... they really don't.
-craig
"You can never have too many knives" -- Logan Nine Fingers
chulett wrote:when someone says they 'need a conditional lookup'... they really don't.
Yup, I agree.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Conditional lookups are fully supported in the parallel Lookup stage. Who was asleep during class?
A server job solution to implement true conditional lookups would require at least two Transformer stages.
You can do it with one Transformer stage and two reference input links, but both lookups will be performed unconditionally. You then need to create constraint expressions to detect what happened and to control data flows accordingly.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.