AW: Create view over multiple files

Hi,

you did not create a view, but a (physical) table with data.
A view is a logical file and must be created with the SQL-Statement CREATE
VIEW.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Lance Gillespie
Gesendet: Wednesday, 29. July 2009 17:01
An: midrange-l@xxxxxxxxxxxx
Betreff: Create view over multiple files

Still learning about SQL.

I want to create a view over the union of two files.

I have worked out the SQL syntax to create a file, but
this does not update when the base files change.

I think this means I want to create a view instead of a file,
but nothing seems to work. Given the SQL below to create
a file with data, how do I modify it to create a view instead?

Thanks,
Lance

create table htedta/GMCVWD01 as (
with
tempfile as (
SELECT
GMTDY as gmyy,
gmtdm as gmmm,
gmtdd as gmdd,
GMFUND as FUND,
GMDPT as DEPT,
GMDIV as DIVISION,
GMSTAB as ACTIVITY,
GMSTAS as subactiv,
GMELM1 as ELEMENT1,
GMELM2 as ELEMENT2,
GMOBJ as OBJECT,
GMPROJ as proj,
GMAPYR as acctyr,
GMAPMO as acctmo,
GMTDSC as desc,
(GMdAMT - GMcAMT) as transamt,
'AJ' AS FLAG
FROM htedta/gm310ap WHERE gmproj <> ' '
UNION ALL
SELECT
GMTDY as gmyy,
gmtdm as gmmm,
gmtdd as gmdd,
GMFUND as FUND,
GMDPT as DEPT,
GMDIV as DIVISION,
GMSTAB as ACTIVITY,
GMSTAS as subactiv,
GMELM1 as ELEMENT1,
GMELM2 as ELEMENT2,
GMOBJ as OBJECT,
GMPROJ as proj,
GMAPYR as acctyr,
GMAPMO as acctmo,
GMTDSC as desc,
GMAMT as transamt,
'AP' AS FLAG
FROM htedta/gm310gp WHERE gmproj <> ' ')
SELECT gmyy, gmmm, gmdd, fund, dept, division, activity, subactiv,
element1, element2, object, proj, acctyr, acctmo, desc,
transamt, flag
from tempfile) with data