DSXchange

Posted: **Tue Dec 26, 2006 7:59 pm**

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

Posted: **Tue Dec 26, 2006 8:46 pm**

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.

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

Posted: **Tue Dec 26, 2006 10:55 pm**

bchau wrote:
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.
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

Posted: **Wed Dec 27, 2006 12:06 am**

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.

Posted: **Wed Dec 27, 2006 12:15 am**

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

Posted: **Wed Dec 27, 2006 12:57 am**

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

Posted: **Wed Dec 27, 2006 7:57 am**

ray.wurlod wrote: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.

Posted: **Wed Dec 27, 2006 12:03 pm**

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 ?

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.

Posted: **Wed Dec 27, 2006 1:06 pm**

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.

Posted: **Fri Dec 29, 2006 8:59 am**

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

Thanks

Posted: **Fri Dec 29, 2006 9:00 am**

Did you completely overlook my question ?

Posted: **Fri Dec 29, 2006 9:05 am**

DSguru2B wrote:Did you completely overlook my question ?

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

Posted: **Fri Dec 29, 2006 9:16 am**

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

Posted: **Fri Dec 29, 2006 9:23 am**

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

Oracle9i

Thanks

Posted: **Fri Dec 29, 2006 9:48 am**

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.

DSXchange

lookup with multiple columns

lookup with multiple columns

Re: lookup with multiple columns

Re: lookup with multiple columns