Dynamically use column names as data to create new columns

[Ronnie]

Please excuse me if I incorrectly approach this reply in any way as this is my first time. And I hope to explain this thoroughly.

I have the same similar situation in my work requirement of a previous posting by "offshore2002" a couple days ago. I wanted to provide a little more detail in my requirements. I have the following:

Two Inputs:
File 1, is the txn file with KEY1 & KEY2 as key and COL3, 4, 5, etc as the transaction (txn) amount columns. COL3 and greater are dynamic in nature as they could change on a quarterly basis. In other words, COL3 might be permanently dropped from the database and COL3A might be added one
quarter.
Example data:
KEY1, KEY2, COL3, COL4, COL5
------------- ------ ------ ------ ------
12-31-2007,"014", 2.99, 3.99, 1.50
12-31-2007,"101", 5.00, 22.15, 99.01
...etc...

File 2, is the lookup file containing the DB2 element mappings to the txn columns to their perspective DB2 table destination.
DB2_ELEM, TXN_COL, DB2_TBL
--------- ---------- ----------
DB2_EL1, COL4, DB2_TBL7
DB2_EL1, COL5, DB2_TBL8
DB2_EL5, COL3, DB2_TBL7
....etc....

Please note there are duplicate DB2 element names (ex. DB2_EL1) but assigned to different TXN_COL columns under different DB2 table names. So I would like to match COL* column names from File 1 to the TXN_COL data in File 2 in order to retrieve its corresponding DB2_ELEM value and DB2_TBL. All this plus carrying over the COL* column data (amount values) from File1 to its DB2 name. The end goal would look like this:

For 12-31-2007, Key2 "014":
DB2_TBL7.DB2_EL1 = 3.99
DB2_TBL7.DB2_EL5 = 2.99
DB2_TBL8.DB2_EL1 = 1.50

For 12-31-2007, Key2 "101":
DB2_TBL7.DB2_EL1 = 22.15
DB2_TBL7.DB2_EL5 = 5.00
DB2_TBL8.DB2_EL1 = 99.01

Any clues to providing a good start would be very much appreciated. I have looked over horizontal pivoting but having trouble in providing data in the screens. I apologize in my searches on the forums in not being able to find this level of detail. Again, any help is greatly appreciated. Thanks!
Ronnie

[kumar_s]

Hi Dzien,
Welcome Aboard :D
Pls post the thread at the right forum from next time. This would go to the Parallel forum rather General.
Since you have the metadata information in Lookup, try to frame the script file which will be executed later to insert data in to the corresponding table.
From input, select the field name and field value as records.
eg
SELECt KEY1,KEY2,COL3, "COL3", COL4, "COL4",COL5, "COL5" from TABLE1

Do a lookup based on the fields where you have the constant towards TXN_COL and match the value so that you get following records

INSERT INTO TABLE2 ..... WHERE .....

Save the file as .sql and finally execute the output file in Target database.

[Ronnie]

Thanks Kumar for replying.

Is it possible you could give me a little more detail in your explanations. Also, I must mention a couple of things before you do that may change your resolutions.
1. You specified hardcoding the column names in the "Select" statement. I will not be able to hard code anything. The DBA redefines the DB2 tables and I import the definitions. From that point on, all I have to work with is the two input files I mentioned in my first posting. There are over 1,300 columns (example, COL3-1303) on my input File 1 and are variable...sometimes it could be 1,300 or 1,400, etc.
2. Once I receive File 1 and process it against File 2, I have some editing to go thru before loading the DB2 tables.

I don't think I can use the Pivot stage because of the dynamic nature of this logic, am I correct in this assumption?

I was thinking of getting DSGetLinkMetaData involved somehow in order to get the File 1 layout and the column positioning and work with it. Just a thought.

Again, any help would be greatly appreciated.

Thanks,
Ronnie