lookup with multiple columns

[vsi]

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

[bchau]

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.

Do you know exactly which fields would contain the "*"? How many of those fields "could" contain "*"?

[vsi]

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.

Do you know exactly which fields would contain the "*"? How many of those fields "could" contain "*"?

any field may contain "*"(except field1) and there could be a chance of 1 to 6 "*" 's for each record from the file.

Thanks

[bchau]

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.

[kumar_s]

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 = '*'......

[ray.wurlod]

"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 ?

[chulett]

Are you expected to bring back all rows that correspond to a partial match on all keys? (This was not stated.)

Actually, that was what looked like to me was stated, or at least to be inferred from the example given.

[vsi]

"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 ?

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.

this is one of the requirement in my project.

[DSguru2B]

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.

[vsi]

Please suggest me the best way of handling this problem by sql query or datastage functionality.

Thanks

[DSguru2B]

Did you completely overlook my question ?

[vsi]

Did you completely overlook my question ?

Out of the 7 fields that are coming from the file, field1 cant have "*" as a value.

[DSguru2B]

What is your database? Maybe i can help you write a sql query that does this for you.

[vsi]

What is your database? Maybe i can help you write a sql query that does this for you.

Oracle9i

Thanks

[DSguru2B]

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.

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;

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.