Database Lookup

srinagesh · Post by **srinagesh** » Mon Jan 16, 2006 11:12 am

Hi !

I am having a problem with the Lookups.

Source File has 1 record (recordid)
Lookup Table (RecordId, RecordName, RecordDetails)
Target (File)

I need to read the source file, lookup for values in the LookupTable where LookupTable.RecordId <= SourceFile.RecordID and populate the resultant rows in the Target file.

I have done a search on "Range Lookup", but couldnt find anything that would give a simple way of doing this.

Could you please suggest a way.

Regards
Nagesh

sjhouse · Post by **sjhouse** » Mon Jan 16, 2006 11:45 am

I would reverse the tables and use the table with multiple records as your source table. Use the other record as a lookup with a hard coded Record ID and use the constrants to filter out records you do not need.

srinagesh · Post by **srinagesh** » Mon Jan 16, 2006 11:59 am

Hi !

The problem is with defining the constraint

Code: Select all

                        Lookup (Singlerecord)
                                      | lnk2
                                      |
                                      v
Src(multiRecordTable)  ----------> Transformer ---------------> Target
                         lnk1                             lnk3

What do I mention in the "KeyExpression" part of the lookup.

sjhouse · Post by **sjhouse** » Mon Jan 16, 2006 12:23 pm

You can hard code the key as a string. If it changes with each run, then take the record (single record item) and write it to a hashed tble with a hard coded key field>

Hashed table with following fields:
Key Field - "Record" (any string to identify the record.
Field 1 - RecordId (from the original table)

Then in your job, put the hashed file key in the KeyExpression "Record"

Make sure you preload the hash table to memory.

Stephen