Page 1 of 2
Lookup Logic
Posted: Fri Dec 02, 2011 5:05 pm
by wittyme
My source table say a has a column Id and there are two reference tables say b and c which has the column Id in both.
The requirement is if the source Id is not found in both b and c then I have to fail the job. The id should be either in b or c.
The constraint fail in look up only works for one link right?
Posted: Fri Dec 02, 2011 5:45 pm
by wittyme
I found the topic. My requirement is something like this
viewtopic.php?t=138424&sid=fce97cc35007 ... 37ebe327bb
Thanks!
Posted: Sat Dec 03, 2011 12:20 pm
by soumya5891
1. Make a union of Id from Table b and Table c in your extraction query.
2. Lookup Table a and the above extraction and mention lookup condition not matched to failure.
Posted: Mon Dec 05, 2011 3:43 pm
by wittyme
b and c are two reference tables ( two links to look up) so what you said don't work right?
Posted: Mon Dec 05, 2011 4:17 pm
by nani1974
Hi,
Yes,...b and c are reference tables as you said... you can do a Union on both those tables and then compare to Source table.
Posted: Mon Dec 05, 2011 4:21 pm
by wittyme
you mean we can write the union logic in the lookup stage?
Posted: Mon Dec 05, 2011 4:27 pm
by nani1974
Hi,
Yes..if you are doing a lookup from a table then you can write a User defined SQL.
Posted: Mon Dec 05, 2011 5:03 pm
by wittyme
So in the look up stage in the constraints under the condition we can write the SQL right? So could you give an example considering the above case? I doubt if I can use the normal SQL
Posted: Mon Dec 05, 2011 5:18 pm
by nani1974
Hi,
I am sorry...what i mean is write a User Defined SQL in your Lookup reference stage and compare both the records in the next stage that is transformer.
Posted: Mon Dec 05, 2011 5:40 pm
by nani1974
Hi,
Sorry about this.....we don't need transformer for this....just in the lookup condition not met then fail...as she mentioned earlier.
Posted: Tue Dec 06, 2011 9:56 am
by wittyme
oh! I know that I can write an user defined sql in the Reference stage and compare in the look-up. By what she said I thought we can write sql in the condition tab of lookup. My reference stage is not oracle its an SFDC stage and I am not good at writing user-defined SQL in the SFDC so that is the reason for asking if there is any way in lookup stage to compare two links.
Anyways. thanks for the response.
Posted: Tue Dec 06, 2011 12:01 pm
by nani1974
SFDC stage?
Posted: Tue Dec 06, 2011 1:40 pm
by soumya5891
1. After extraction of b add a transformer and default a colunmn say COL1 with some default value and declared as nullable yes
2. Then first look up with a an b with a as stream and set both as continue.
3.Look up the record set with with c.In the look up condition gives COL1 is null.And set the options as continue and Failed,
Hope it will work
Posted: Wed Dec 07, 2011 10:25 am
by wittyme
Well it has been resolved. I followed the method ray suggested in the link which I posted earlier.
Thanks.
Posted: Wed Dec 07, 2011 3:02 pm
by ray.wurlod
The sensible one or the silly one?
