lookup with multiple columns
Moderators: chulett, rschirm, roy
lookup with multiple columns
I have a file with fields field1,field2,field3,field4,field5,field6,field7. and a table with columns col1,col2,col3,col4,col5,col6,col7,col8. i have to perform a lookup from file to the table and retrieve the col8 from the table.
some of the fields of the file may contain "*" as values, then match with the remaining columns and retrieve the col8 from table.
example :record in the file 101,10,*,30,40,50,60
in the table 101,10,20,30,40,50,60,1001
101,10,70,30,40,50,60,1002
result should be 1001
1002
need ur help to solve this.
Thanks in advance
some of the fields of the file may contain "*" as values, then match with the remaining columns and retrieve the col8 from table.
example :record in the file 101,10,*,30,40,50,60
in the table 101,10,20,30,40,50,60,1001
101,10,70,30,40,50,60,1002
result should be 1001
1002
need ur help to solve this.
Thanks in advance
Re: lookup with multiple columns
Do you know exactly which fields would contain the "*"? How many of those fields "could" contain "*"?vsi wrote:I have a file with fields
some of the fields of the file may contain "*" as values, then match with the remaining columns and retrieve the col8 from table.
Re: lookup with multiple columns
any field may contain "*"(except field1) and there could be a chance of 1 to 6 "*" 's for each record from the file.bchau wrote:Do you know exactly which fields would contain the "*"? How many of those fields "could" contain "*"?vsi wrote:I have a file with fields
some of the fields of the file may contain "*" as values, then match with the remaining columns and retrieve the col8 from table.
Thanks
wow that is nasty. The number of combinations is outrageous. I was thinking a separate lookup for each combination but 462 lookups is too much.
I am sorry, perhaps some of the other professionals would be able to help you with this.
This may be possible using awk, but I dunno how good it would be performance-wise.
I am sorry, perhaps some of the other professionals would be able to help you with this.
This may be possible using awk, but I dunno how good it would be performance-wise.
I dont have access to PX right no, but you can try using the Condition option available in Lookup stage.
Code: Select all
File.Field1 = Table.Col1 or File.Field1 = '*'......
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
"Outrageous" is a good adjective for this requirement.
Are you expected to bring back all rows that correspond to a partial match on all keys? (This was not stated.)
Can you - or get "them" to - explain in English what is required, and why ?
Are you expected to bring back all rows that correspond to a partial match on all keys? (This was not stated.)
Can you - or get "them" to - explain in English what is required, and why ?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
if some of the fields in the file have "*" as values, then try to match with the remaining keys and retrieve 'col8(from table) ' for all the matched rows.ray.wurlod wrote:"Outrageous" is a good adjective for this requirement.
Are you expected to bring back all rows that correspond to a partial match on all keys? (This was not stated.)
Can you - or get "them" to - explain in English what is required, and why ?
this is one of the requirement in my project.
Can you assure that atleast one column will never be a *. The more you can assure the better. If you can assure that then you can load your file to a temp table in your database and write a sql query to get this done. The sql query wont be pretty though. But you shouldnt care, your requirement isnt pretty either.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Ok here is what i can offer. Load your file into a temp table in the same schema and run this query. Replace the table names and column names in my query. This is off of memory and is not tested. You might bump into a few errors, but they will be minor. As far as i can see it, its logically correct and should work.
What i am doing here is checking if the value is '*', i am replacing it with the value present in your database table keyed on Col1. Now you see why i asked for a single column that can never be an *.
After handling any occurance of * i am doing a join on all the seven columns and getting column 8. The driver table is your temp table that now contains the contents of your file.
Code: Select all
SELECT F.col1,
(CASE F.col2
WHEN '*' THEN (SELECT sub_D.col2 from DataBaseTable sub_D
where sub_D.col1 = F.col1)
ELSE F.col2) F.finCol2
(CASE F.col3
WHEN '*' THEN (SELECT sub_D.col3 from DataBaseTable sub_D
where sub_D.col1 = F.col1)
ELSE F.col2) F.finCol3
(CASE F.col4
WHEN '*' THEN (SELECT sub_D.col4 from DataBaseTable sub_D
where sub_D.col1 = F.col1)
ELSE F.col2) F.finCol4
(CASE F.col5
WHEN '*' THEN (SELECT sub_D.col5 from DataBaseTable sub_D
where sub_D.col1 = F.col1)
ELSE F.col2) F.finCol5
(CASE F.col6
WHEN '*' THEN (SELECT sub_D.col6 from DataBaseTable sub_D
where sub_D.col1 = F.col1)
ELSE F.col2) F.finCol6
(CASE F.col7
WHEN '*' THEN (SELECT sub_D.col7 from DataBaseTable sub_D
where sub_D.col1 = F.col1)
ELSE F.col2) F.finCol7
D.col8
From TempTableFromFile F
LEFT OUTER JOIN DataBaseTable D
ON F.col1 = D.col1 AND
F.finCol2 = D.col2 AND
F.finCol3 = D.col3 AND
F.finCol4 = D.col4 AND
F.finCol5 = D.col5 AND
F.finCol6 = D.col6 AND
F.finCol7 = D.col7;
After handling any occurance of * i am doing a join on all the seven columns and getting column 8. The driver table is your temp table that now contains the contents of your file.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.