Re: ETL - how to handle codes: need to expand old codes to more descriptive text to pass to other systems

Could you please assist with this SQL view?

I have two files:

State file
----------------
CA California
MN Minnesota
TX Texas


Regional office file
---------------------
MW Midwest
SW Southwest

I would like to combine the two files into an SQL view with the resulting file (and new CodeType field) as:


CodeDesc file
-----------------------

CodeType CodeDesc Desc
REGION MW Midwest
REGION SW Southwest
STATE CA California
STATE MN Minnesota
STATE TX Texas



I tried the following but it returns error. Any suggestions?


create view codedesc (code, desc)
as (select ASTABRD,ASTNM from state)
, (select ARGNOFA,ARGNOFD from regoff)
Token , was not valid. Valid tokens: <END-OF-STATEMENT>.


Thanks!



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Monday, June 25, 2012 9:06 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: ETL - how to handle codes: need to expand old codes to more descriptive text to pass to other systems

On 25 Jun 2012 17:24, Stone, Joel wrote:

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?

<<SNIP>>

* SQL: select the description of a code from each
corresponding file (downside - slow for transforming entire large
files?)

Replacing encoded values with expanded replacement values is easily
effected with a UDF, or when the correlated data is already in a TABLE,
then just as easily composed as another SELECT. So perhaps just code
the replacement values effect in a VIEW:

create view cust_order_etl as
( select ...
,( select sn.state_name
from STATE sn
where sn.state_code = co.cust_state_code )
as cust_state_name
,( select os.order_status
from ORDER_STATUS os
where os.order_status_code = co.cust_order_status_code )
as cust_order_status
,...
from ... co ...
)

Regards, Chuck