I have a table the table contains key data + 52 multiple index values identified as index1.....index52. I would like to write ten records from one record such as the new record will have the rows as follows:
Key value ..... index1 key value ......index2 .. .. .. key value .......index52.
I could use the union command to select each index column + key value by having 52 select statements. The idea does not appeal me. I would like another alternate way to do this.
Thanks for your great suggestion. The following query worked using your approach
/* CROSS JOIN to a table with 52 rows (e.g. just SMALLINT values 1 to 52) and use CASE to determine the value of the "INDEXn" field. Is that any better? */ /* CREATE SET TABLE DTLOADT01.test_occurrence ,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( col_num SMALLINT FORMAT '99' NOT NULL) PRIMARY INDEX ( col_num );
*/ select id_profile, col_num,
(case col_num when 1 then ind_seasonal01 when 2 then ind_seasonal02 when 3 then ind_seasonal03 when 4 then ind_seasonal04 when 5 then ind_seasonal05 end ) index_value
from dtmmit01.tmes022_profile_slim a, dtloadt01.test_occurrence order by 1, 2