Lookup Logic

wittyme · Post by **wittyme** » Fri Dec 02, 2011 5:05 pm

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?

wittyme · Post by **wittyme** » Fri Dec 02, 2011 5:45 pm

I found the topic. My requirement is something like this

viewtopic.php?t=138424&sid=fce97cc35007 ... 37ebe327bb

Thanks!

soumya5891 · Post by **soumya5891** » Sat Dec 03, 2011 12:20 pm

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.

wittyme · Post by **wittyme** » Mon Dec 05, 2011 3:43 pm

b and c are two reference tables ( two links to look up) so what you said don't work right?

nani1974 · Post by **nani1974** » Mon Dec 05, 2011 4:17 pm

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.

wittyme · Post by **wittyme** » Mon Dec 05, 2011 4:21 pm

you mean we can write the union logic in the lookup stage?

nani1974 · Post by **nani1974** » Mon Dec 05, 2011 4:27 pm

Hi,
Yes..if you are doing a lookup from a table then you can write a User defined SQL.

wittyme · Post by **wittyme** » Mon Dec 05, 2011 5:03 pm

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

nani1974 · Post by **nani1974** » Mon Dec 05, 2011 5:18 pm

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.

nani1974 · Post by **nani1974** » Mon Dec 05, 2011 5:40 pm

Hi,
Sorry about this.....we don't need transformer for this....just in the lookup condition not met then fail...as she mentioned earlier.

wittyme · Post by **wittyme** » Tue Dec 06, 2011 9:56 am

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.

nani1974 · Post by **nani1974** » Tue Dec 06, 2011 12:01 pm

SFDC stage?

soumya5891 · Post by **soumya5891** » Tue Dec 06, 2011 1:40 pm

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

wittyme · Post by **wittyme** » Wed Dec 07, 2011 10:25 am

Well it has been resolved. I followed the method ray suggested in the link which I posted earlier.

Thanks.

ray.wurlod · Post by **ray.wurlod** » Wed Dec 07, 2011 3:02 pm

The sensible one or the silly one?