How to achieve cross multiplication?

[rsrikant]

Hi,

I have 5 columns in the source table - table 1
Lets say Key, col1, col2, col3.... col5.


I have another table with just 1 column and this column has all the column names of first table as records (except the key).
So, there are 5 records in the table2.

I have one more table which has some 3 columns table 3.
Key, value1, value2.

My target has 5 columns.
Key, columnname, columnvalue, value1, value2

Now i will lookup table1 with table3. For each matching key value i have to populate my target like

Key1, col1, value in col1 for matching key, value in value1 for matching key, value in value2 for matching key
Key1, col2, value in col2 for matching key, value in value1 for matching key, value in value2 for matching key
...
...
Key1, col5, value in col5 for matching key, value in value1 for matching key, value in value2 for matching key
Key2, col1, value in col1 for matching key, value in value1 for matching key, value in value2 for matching key
Key2, col2, value in col2 for matching key, value in value1 for matching key, value in value2 for matching key
...
...
Key2, col5, value in col5 for matching key, value in value1 for matching key, value in value2 for matching key
Key3, col1, value in col1 for matching key, value in value1 for matching key, value in value2 for matching key
...
...

How to achieve something like this.

Key1, Key2 are the matching key values.
col1, col2 are the column names of table1 which is same as the record values in table2.
3rd column is populated with the value in table1 for the matching key as well as the corresponding value in that particular column (the column is determined by the 2nd col in the target table).
4th and 5th cols in the target table are to be copied from 2nd and 3rd column in table3 for the matching key value.

I don't know if i am not explaining my situation well.
If you guys have any idea on how to do this please let me know.

Thanks in advance.
Srikanth

[ray.wurlod]

Please let me clarify. What you're trying to do here is to use metadata (column name) as the basis of a lookup reference key expression. Is this correct?

Any solution to this will be messy, so please verify my understanding before I waste time giving incorrect answers.

[rsrikant]

Yes. Your understanding is right Ray.

Any idea on how to achieve this.

Thanks,
Srikanth

[kduke]

Srikanth

You could put the lookups in hash files and do 5 lookups. Hard code the key values to the column names. I would do this in a function. Initialize the stage variables with one read for each column. Then the lookups are only done once.

[ray.wurlod]

What Kim suggested is exactly what I was going to suggest once I'd clarified that's what you were seeking to do.