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

Joel, I don't know what example from Chuck you are referring to, but I'm not familiar AT ALL with the syntax you posted... Can you point me toward Chuck's suggestion?

I think you misunderstand what UNION is doing. It is the only solution that I'm aware of to the type of problem you're trying to solve.

You can have MANY tables unioned together, the only restriction being that column types must be similar in each unioned result set...

-Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Monday, July 09, 2012 5:30 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

Thanks for the tip but I want to include many more tables. I think that UNION is more to join RELATED files.

I am trying to get CRPence's example to work but not sure how.

To be clear, the tables are NOT related in any way.

How can I get the following to function?

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>.


Here is a better example:

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


Artist file (totally unrelated to state file)
-------------------------------------------
BEA Beatles
ROL Rolling Stones
BS Britney Spears


The view will show all unrelated files as follows:

CodeFile
________________
CodeType CodeDesc Desc
ARTIST BEA Beatles
ARTIST ROL Rolling Stones
ARTIST BS Britney Spears
STATE CA California
STATE MN Minnesota
STATE TX Texas



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vern Hamberg
Sent: Monday, July 09, 2012 4:55 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

Joel

For this kind of thing I use a UNION -

create view codedesc (codetype, code, desc)
as select 'REGION',ARGNOFA,ARGNOFD from regoff
union select 'STATE',ASTABRD,ASTNM from state


On 7/9/2012 4:21 PM, Stone, Joel wrote:

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