Re: This small query not compiling

On 12/9/2015 11:02 AM, Hoteltravelfundotcom wrote:

this is strange thing now.. OEINDLID is a PF.

this select works
select idord#, idprt#, sum(idshp# * idntu$) from astdta.OEINDLID WHERE
idprt# not
like 'FRT%' AND iddocd > 20150930
group by idord#, idprt#

but when trying to create a view of this

CREATE VIEW ordamtno as
select idord#, idprt#, sum(idshp# * idntu$) from astdta.OEINDLID WHERE
idprt# not
like 'FRT%' AND iddocd > 20150930
group by idord#, idprt#

i get error sql 0153 column list required.

what do i need to put here?

This particular error message is extremely clear.

The DB2 reference is also very clear on this topic.
http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzhcview.htm

'(column-name, … )
Names the columns in the view. If a list of column names is
specified, it must consist of as many names as there are columns in the
result table of the fullselect. Each column-name and system-column-name
must be unique and unqualified. If a list of column names is not
specified, the columns of the view inherit the names of the columns and
system names of the columns of the result table of the fullselect.

A list of column names (and system column names) must be specified
if the result table of the subselect has duplicate column names,
duplicate system column names, or an unnamed column. For more
information about unnamed columns, see Names of result columns.'

'Names of result columns
If the AS clause is specified, the name of the result column is the
name specified on the AS clause.

If the AS clause is not specified and a column list is specified in
the correlation clause, the name of the result column is the
corresponding name in the correlation column list.

If neither an AS clause nor a column list in the correlation clause
is specified and the result column is derived only from a single column
(without any functions or operators), then the result column name is the
unqualified name of that column.

If neither an AS clause nor a column list in the correlation clause
is specified and the result column is derived only from a single
variable (without any functions or operators), then the result column
name is the unqualified name of that variable.

If neither an AS clause nor a column list in the correlation clause
is specified and the result column is derived only from a single pseudo
column (without any functions or operators), then the result column name
is the name of that pseudo column.

All other result columns are unnamed.'