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

Something like this, maybe:

create view codedesc (codetype, code, desc) as
(select 'State', statecde, statename from statefile)
union
select 'Artist', art_cde, art_name from Artistfile)
union
...
)

Not sure what will happen if the fields in the different files have different lengths. You may have to put the longest first.

Or maybe try

(select varchar('State',20), ... as needed.

However, in the green screen STRSQL I just ran this, which should also work on your machine:

create view qtemp/ccc (type, code, desc) as (select
'type1', lstnam, state from qiws/QCUSTCDT union
select 'MuchLonger', state, lstnam from qiws/QCUSTCDT)
View CCC created in QTEMP.

Sam


On 7/9/2012 6: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