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

[Sam_L <lennon_s_j@xxxxxxxxxxx>]

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