Column output list based on reference data
Posted: Tue Oct 06, 2015 2:47 pm
Hi all,
We have a problem that we have been trying to solve without much success and your help would be much appreciated.
The requirement is to prepare a record to be written to output in XML format of which two fields are complex type and can be populated any number of times.
The scenario is as follows:
1. A long list of source columns (about 90) from couple of joined tables
2. A dynamic number of reference records of which column name values are the detail we need
3. We then need to use the above two to come up with the column name/value pairs to populate into complex XML type
Some sample data below to clarify the requirement
Input
PK| BK| COL_A| COL_B| COL_C| COL_D| COL_E| COLxxx
-------------------------------------------------------------------------------
23| Cus_123| 5| 15| 25| 35| 40
Reference
COL_ID| COL_NAME
-------------------------
3| COL_A
6| COL_B
8| COL_E
Output
PK| BK| COL_A| COL_B| COL_E
--------------------------------------------------
23| Cust_123| 590| 15| 40
Alternative Output
PK| BK| COL_NAME| COL_VALUE
--------------------------------------------------
23| Cust_123| COL_A| 590
23| Cust_123| COL_B| 15
23| Cust_123| COL_E| 40
So we need to read the column name values from the reference data to determine which input column name/value pairs to take and in this case only COL_A, COL_B and COL_E are taken. These column name/value pairs will end up going to xml complex type but we should be fine with this part.
Would anyone have any suggestions on the best way to do this. Any routines are highly undesirable because this needs to fit into an existing code.
Many thanks,
Novak
We have a problem that we have been trying to solve without much success and your help would be much appreciated.
The requirement is to prepare a record to be written to output in XML format of which two fields are complex type and can be populated any number of times.
The scenario is as follows:
1. A long list of source columns (about 90) from couple of joined tables
2. A dynamic number of reference records of which column name values are the detail we need
3. We then need to use the above two to come up with the column name/value pairs to populate into complex XML type
Some sample data below to clarify the requirement
Input
PK| BK| COL_A| COL_B| COL_C| COL_D| COL_E| COLxxx
-------------------------------------------------------------------------------
23| Cus_123| 5| 15| 25| 35| 40
Reference
COL_ID| COL_NAME
-------------------------
3| COL_A
6| COL_B
8| COL_E
Output
PK| BK| COL_A| COL_B| COL_E
--------------------------------------------------
23| Cust_123| 590| 15| 40
Alternative Output
PK| BK| COL_NAME| COL_VALUE
--------------------------------------------------
23| Cust_123| COL_A| 590
23| Cust_123| COL_B| 15
23| Cust_123| COL_E| 40
So we need to read the column name values from the reference data to determine which input column name/value pairs to take and in this case only COL_A, COL_B and COL_E are taken. These column name/value pairs will end up going to xml complex type but we should be fine with this part.
Would anyone have any suggestions on the best way to do this. Any routines are highly undesirable because this needs to fit into an existing code.
Many thanks,
Novak