× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.