Page 1 of 1
how to retrive unmatching records from lookup
Posted: Thu Dec 23, 2004 3:09 am
by suma
Hi all,
I have a problem with lookup.
In the source say i have 5 records,But in the Look up i have only 3 matching records.When i make a loop up i get the records that are matching. To get the records that are not matching i give the condition in the constrains IsNull(DSLink.1) where DSLink1 is the lookup link.
But everytime i do i get only 0.No records are loaded for the unmatching records.
Help me ut to solve this problem
Posted: Thu Dec 23, 2004 3:44 am
by anitha_casturi
Ideally IsNull(DSLink.1) should work.
Why not try ISNULL(match column) and check????
Posted: Thu Dec 23, 2004 8:27 am
by MaheshKumar Sugunaraj
Hi,
You could try the following:
hashfile_lookup.NOTFOUND in the constraint.
Should work.
Thanks & Regards
Mahesh
Posted: Thu Dec 23, 2004 8:51 am
by Mike
If I've understood your problem: you want to obtain the records in your lookup file that do not exist in your stream input file. You will have to rethink your design strategy. A reference lookup is essentially a left outer join between your stream input and your reference input. It sounds like you're looking for a full outer join solution.
Mike
Posted: Thu Dec 23, 2004 3:00 pm
by ray.wurlod
Re-read what you asked, which Mike has highlighted. The purpose of a lookup is to find what's there. If it's not there you can't find it! Therefore there will be no "unmatching records".
As Mike said, it appears that you're seeking to perform a full outer join (or union); all values irrespective of source. If both sources are in the same database the easiest way to do this is with SQL (either a full outer join query or a union query).
look up work?
Posted: Thu Dec 23, 2004 4:06 pm
by changming
look up can solve this problem, i think,
a lookup will find what is match, if not find, return null. so you capture the null value, instead of not null value.
Posted: Thu Dec 23, 2004 5:25 pm
by ray.wurlod
Did you build your constraint expression using the expression editor or by manual typing?
The IsNull() function must apply to the key column on the reference input link (the lookup) - for example IsNull(DSLink1.keycolumn)
Your original request suggested that you may not have this totally correctly specified.
Posted: Fri Dec 24, 2004 12:00 am
by mandyli
Hi
As ray said for retrieving unmatched records use Join stage(left outer Join). Lookup stage used for chekcing any match value is there in table or file.
please try use join stage.
Thanks
Man
Posted: Fri Dec 24, 2004 9:56 am
by chulett
mandyli wrote:please try use join stage.
Doesn't really help telling someone to use a PX stage for a Server job.
I think we're at the point in this thread where we need to wait for the OP to come back and comment on all the wonderful advice here. Otherwise, we're just talking to ourselves.
