Issue with lookup with only one key column
Posted: Tue Jul 05, 2011 8:35 am
Hello,
Right now I am facing one issue during the job design. My source is oracle and have the following fields: ISO_COUNTRY3, NAME, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4 and ADDRESS5. And my lookup hashed-file have the fields ISO_COUNTRY3 (Key), COMPANY_NAME. The business rule requirement is, scan all provided company names from lookup with source fields NAME, ADDRESS1 to ADDRESS5. If a match found then the customer should be identified as <OUTPUT>.Company = 'O'. The problem here is, since I've only one key column i.e., ISO_COUNTRY3 and there was no other key columns available. In the lookup concept, it will first to match with <INPUT>.ISO_COUNTRY3 = <LKP>.ISO_COUNTRY3 then will find the <LKP>.COMPANY_NAME. But lookup does have lots of company_name with <LKP>.ISO_COUNTRY3 same value. Such as:
<LKP>.ISO_COUNTRY3 --> <LKP>.COMPANY_NAME
GBR --> XYZ LIMITED
GBR --> PHILIPS RADIO
GBR --> COMP SCIENCE LIMITED
.
.
.
.
Now, <INPUT>.ISO_COUNTRY3 = <LKP>.ISO_COUNTRY3 matches then will return the value. But I've to check the <LKP>.COMPANY_NAME against <INPUT>.NAME, <INPUT>.ADDRESS1 to <INPUT>.ADDRESS5. Where here the input value not sure in which field it will be loading. For example, XYZ Limited value from Input field coming as <INPUT>.NAME = 'XYZ'; <INPUT>.ADDRESS1 = 'LIMITED'. If this will be the case I can't take COMPANY_NAME also as Lookup key column.
Need your suggestions If there was any possibility with different design. Thanks your help in advance.
Right now I am facing one issue during the job design. My source is oracle and have the following fields: ISO_COUNTRY3, NAME, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4 and ADDRESS5. And my lookup hashed-file have the fields ISO_COUNTRY3 (Key), COMPANY_NAME. The business rule requirement is, scan all provided company names from lookup with source fields NAME, ADDRESS1 to ADDRESS5. If a match found then the customer should be identified as <OUTPUT>.Company = 'O'. The problem here is, since I've only one key column i.e., ISO_COUNTRY3 and there was no other key columns available. In the lookup concept, it will first to match with <INPUT>.ISO_COUNTRY3 = <LKP>.ISO_COUNTRY3 then will find the <LKP>.COMPANY_NAME. But lookup does have lots of company_name with <LKP>.ISO_COUNTRY3 same value. Such as:
<LKP>.ISO_COUNTRY3 --> <LKP>.COMPANY_NAME
GBR --> XYZ LIMITED
GBR --> PHILIPS RADIO
GBR --> COMP SCIENCE LIMITED
.
.
.
.
Now, <INPUT>.ISO_COUNTRY3 = <LKP>.ISO_COUNTRY3 matches then will return the value. But I've to check the <LKP>.COMPANY_NAME against <INPUT>.NAME, <INPUT>.ADDRESS1 to <INPUT>.ADDRESS5. Where here the input value not sure in which field it will be loading. For example, XYZ Limited value from Input field coming as <INPUT>.NAME = 'XYZ'; <INPUT>.ADDRESS1 = 'LIMITED'. If this will be the case I can't take COMPANY_NAME also as Lookup key column.
Need your suggestions If there was any possibility with different design. Thanks your help in advance.