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

Looks to me like you need to UNION the two SQL's to produce your list of values...

Create view codedesc (CodeType char(6), CodeDesc, Desc) as
( Select 'STATE', ASTABRD, ASTNM from state
UNION
Select 'REGION', ARGNOFA, ARGNOFD from regoff
)

-Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Monday, July 09, 2012 4:21 PM
To: Midrange Systems Technical Discussion
Subject: 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