On 22 Sep 2013 12:25, Hoteltravelfundotcom wrote:
This view is fine without the GROUP BY. But I really need that.
Is there a way to fix this code so that it would be GROUP BY?
<ed: addendum: The following CREATE request fails with SQL0122:>
Column IDCOM# or expression in SELECT list not valid.
CREATE VIEW ASTCCDTA.QTYUSAGE AS
SELECT IARCC9, IDDOCD, IDCOM#, ADSFX#, IDPRT#, IDGRC#
, IDENT#, IDSFX#, IDPRLC, IDNTU$, IDSHP#
, ASTDTA.ADRESSAD.ADFNM , ASTDTA.ADRESSAD.ADLNM
, ASTDTA.ADRESSAD.ADZIPC , ASTDTA.OEINHDIH.IHORD#
, ASTDTA.ICPRTMIA.IA101 , ASTDTA.OEINHDIH.IHVIAC
, ASTDTA.ADRESSAD.ADSTTC , IDINV#
<<SNIP>>
GROUP BY ASTDTA.ICPRTMIA.IARCC9
, ASTDTA.OEINDLID.IDDOCD
As the message SQL0122 suggests is a possible origin, the "Column
name IDCOM# is specified in the SELECT clause but not in the GROUP BY
clause" and recovery possibly can be effected by, "If a GROUP BY clause
is required, make certain that all columns or expressions in the SELECT
list and ORDER BY clause are also in the GROUP BY clause." In so few
words, the recovery is not exact, but probably sufficient without
requiring a better understanding. So hopefully more accurately...
Specify all of the other non-aggregate columns from the select-list,
also in the GROUP BY [or just use the DISTINCT keyword in the SELECT and
omit the GROUP BY to get the same effect]. Or... Either remove all of
the non-grouped columns from the select-list [i.e. pare the column-list
of the SELECT to the first two] or place each of the non-grouped columns
inside an aggregate function. Or perhaps the effects of grouping-sets
is desirable [vs that of the simple GROUP BY]... but without any
aggregates shown in the query, what is intended, is quite unfathomable.
Perhaps the intention is to produce a /report/ rather than a
/result-set/ such that the inquiry is not about what the SQL SELECT
statement selects, but about what the report writer should effect.? If
indeed the result-set must be a group, then...
Perhaps if the overall idea can be recomposed using just the one
database file QCUSTCDT in QIWS in a FROM clause of a SELECT query to get
just detail [non-summarized] rows, additionally describing the desired
effects from having /grouped/ that data, then possibly the intention
better could be portrayed to the reader; i.e. a join is irrelevant, as
is the CREATE VIEW. So for example the first of the following three
queries gets all detail rows from selected columns of that sample data,
and the second query fails for the same -122 reason as the query in the
quoted message, and the third query is a valid grouping query with a SUM
aggregate expression:
select state, city, cdtlmt, baldue
from qiws/qcustcdt
select state, city, cdtlmt, baldue
from qiws/qcustcdt
group by state
select state, city, dec( sum( baldue ), 9, 2) as sumbaldue
from qiws/qcustcdt
group by state, city
Or if maybe some other reduced data-set from just one file could
suffice to express what the outcome would be; e.g. a few columns and
several rows of the result-set of the non-grouped VIEW perhaps?
midrange.com
