A new reporting requirement has arisen and I'm not too sure of the best way to tackle it. The source system has a field in it - let's call the field 'Fruit codes'. The Fruit codes field contains a list of comma-separated fruits. These are stored as semi-meaningful values Eg.
ID - Fruit codes
100 - APL, BAN, STRW
101 - ORNG
102 - BAN, STRW
There is a table that maps these semi-meaningful values to the full string equivalent. Eg.
Fruit code - Fruit name
APL - Apple
BAN - Banana
STRW - Strawberry
ORNG - Orange
We want to be able to display the full-string equivalent, separated by commas. The expected output should look like this:
ID - Fruit names
100 - Apple, Banana, Strawberry
101 - Orange
102 - Banana, Strawberry
We are using DataStage 9.1 with DB2 9.7. I was hoping that I might be able to use the Ereplace function in DataStage, however I'm not sure that this will work. The list of possible Fruits changes every now and then, so I want this to be dynamic. I wonder whether I might need to loop through each of the comma-separated list of fruits and then somehow to an Ereplace using the mapping table. Perhaps I will need to separate the comma-separated lists into individual rows or columns.
Maybe it's possible to do this using the Pivot stage, or the opposite of the LISTAGG DB2 function (if this exists). I'm not so proficient in DataStage, so I have lots of ideas, but no answers!
Thank you so much for your help.