We're running db2 udb v8.1 on zLinux. I'm getting syntax errors
creating a summary table. We've done this before successfully; I
can't pinpoint the problem in this one. There are two see-saw errors.
Without the "as..." clause on a column, I get "duplicate name"; with
the "as ..." clause I get "invalid fullselect". The fullselect runs
successfully removed from the create statement with or without the
"as...". Hopefully some sharp eyes in the audience can spot the
problem.

This is the version with the "as..." clauses.

create summary table mysummary.table as
(
SELECT
Person.AccessNumber
, PC.ContactActionTaken
, CA.Description as ActionDesc
, PC.ContactDate
, PC.ContactForm
, CB.Description as ContactDesc
, PC.ContactIntCode
, CC.Description as IntDesc
, PC.ContactTime
, PC.ContextCode
, PC.Notes1 concat rTRIM (PC.Notes2) concat rTRIM(PC.Notes3) concat
rTRIM(PC.Notes4) concat rTRIM(PC.Notes5)
, V.NAMESORTFIELD
, PC.UpdateNatUser
, PC.UpdateTimestamp
, PC.PersonId
FROM
View1 PERSON,
View2 PC
LEFT OUTER JOIN View3 CA
ON PC.ContactActionTaken = CA.Code
LEFT OUTER JOIN View4 CB
ON PC.ContactForm = CB.Code
LEFT OUTER JOIN View5 CC
ON PC.ContactIntCode = CC.Code
LEFT OUTER JOIN View6 V
ON PC.UCCONTACTID = V.PersonID AND V.CURRENTID = 'Y'
WHERE PERSON.PERSONID = PC.PERSONID
and PC.ContextCode not in ('COLL', 'ADM', 'TELC')
)
data initially deferred refresh deferred
;

Many thanks for any help,

Mary Lou Arundell

Don't know if it's the same in v8.1, but in v7.2, you get an error on the
creation of a summary table if the fullselect has a column that is not named
(such as in your concatenated column you're returning.). If running the
fullselect by itslef produces any column with an unnamed header, then the
resultant summary table won't be able to be referenced by name for that
column. Try adding an alias in your select statement:

PC.Notes1 concat rTRIM (PC.Notes2) concat rTRIM(PC.Notes3) concat
rTRIM(PC.Notes4) concat rTRIM(PC.Notes5) AS PCNOTES


Evan


"mla" <arundeml@ucmail.uc.edu> wrote in message
news:ba7e2ac9.0310101152.822730a@posting.google.co m...
> We're running db2 udb v8.1 on zLinux. I'm getting syntax errors
> creating a summary table. We've done this before successfully; I
> can't pinpoint the problem in this one. There are two see-saw errors.
> Without the "as..." clause on a column, I get "duplicate name"; with
> the "as ..." clause I get "invalid fullselect". The fullselect runs
> successfully removed from the create statement with or without the
> "as...". Hopefully some sharp eyes in the audience can spot the
> problem.
>
> This is the version with the "as..." clauses.
>
> create summary table mysummary.table as
> (
> SELECT
> Person.AccessNumber
> , PC.ContactActionTaken
> , CA.Description as ActionDesc
> , PC.ContactDate
> , PC.ContactForm
> , CB.Description as ContactDesc
> , PC.ContactIntCode
> , CC.Description as IntDesc
> , PC.ContactTime
> , PC.ContextCode
> , PC.Notes1 concat rTRIM (PC.Notes2) concat rTRIM(PC.Notes3) concat
> rTRIM(PC.Notes4) concat rTRIM(PC.Notes5)
> , V.NAMESORTFIELD
> , PC.UpdateNatUser
> , PC.UpdateTimestamp
> , PC.PersonId
> FROM
> View1 PERSON,
> View2 PC
> LEFT OUTER JOIN View3 CA
> ON PC.ContactActionTaken = CA.Code
> LEFT OUTER JOIN View4 CB
> ON PC.ContactForm = CB.Code
> LEFT OUTER JOIN View5 CC
> ON PC.ContactIntCode = CC.Code
> LEFT OUTER JOIN View6 V
> ON PC.UCCONTACTID = V.PersonID AND V.CURRENTID = 'Y'
> WHERE PERSON.PERSONID = PC.PERSONID
> and PC.ContextCode not in ('COLL', 'ADM', 'TELC')
> )
> data initially deferred refresh deferred
> ;
>
> Many thanks for any help,
>
> Mary Lou Arundell