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



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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
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.