Re: Group by issue

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?