You have code tables with descriptions of what field values mean? Wow! I wish I had these. : )
(Tongue firmly in cheek. But I am not being entirely facetious.)
I have many files with data in fields like TBFLG1, TBFLG2, TBFLG3, etc. with values that could mean just about anything. (Or so it seems).
Paul
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Monday, June 25, 2012 5:24 PM
To: 'Midrange Systems Technical Discussion'
Subject: ETL - how to handle codes: need to expand old codes to more descriptive text to pass to other systems
I am extracting data from a legacy system and passing to a data warehouse.
I need to send the data elements, but they also want to expand codes into meaningful code descriptions.
For example, if the customer-address field shows the state as "TX", I pass the "TX", and I also need to read the STATE table and expand the "TX" code to "Texas".
Another example would be order-status. If the order status is "N", I have to read the ORDER-STATUS table and send the "N" along with the string "NEW ORDER".
Well it turns out that there are dozens (maybe a hundred) "code" files where various codes are expanded to meaningful words to show on screens and reports.
What is a good way to handle this?
* Brute force method: define each code file in the pgm and read it to get the description (Downside - lots of messy pgms with lots of files defined)
* SQL: select the description of a code from each corresponding file (downside - slow for transforming entire large files?)
* Is there a way to dynamically read a file using the keys found in system file QADBKFLD without adding each file to a pgm?
* Build ONE temp file out of all the code description files (using SQL and CL), and add a key such as "STATE" or "ORDER-STATUS". Then the ETL pgms can read only THAT file for the code descriptions.
How have others handled this for ETL projects? I am leaning towards the last option. Any downside to this? Other ideas?
Thanks!
______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit
http://www.symanteccloud.com ______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
