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

Joel

UNION is for combining possibly disparate data sets - all you need to do is make the columns of each SELECT of the UNION have the same data types, respectively.

JOIN, as Joe said, is for relating tables - you JOIN "ON" a set of columns from both tables or result sets.

__The example I gave you did work - try it out.__

So you may have to cast certain fields to get the data types right. You can UNION SELECT all you want - the green-screen editor doesn't support UNION SELECT in a CREATE VIEW, but it can be done.

I think you are trying for a kind of JOIN, by having the commas. But you don't have an overall SELECT ? FROM to make it work. Besides, the comma-syntax for JOINs is not a good idea, especially as you add more of them - in recent releases the specification of JOINs has to be more rigorous.

But you really don't want a JOIN, anyhow. Think of a JOIN (INNER variety) as an intersection in set theory (elements that are in both sets only), a UNION as a union in set theory (elements that are in either set).

HTH
Vern

On 7/9/2012 5:30 PM, Stone, Joel wrote:

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