From the SQL Reference Redbook:
If GROUP BY or HAVING is used
v Each column-name in the select list must identify a grouping expression, or be
specified within an aggregate function, or be a correlated reference:
- If the grouping expression is a column name, the select list may apply
additional operators to the column name. For example, if the grouping
expression is column C1, the select list may contain C1+1.
- If the grouping expression is not a column name, the select list may not apply
additional operators to the expression. For example, if the grouping
expression is C1+1, the select list may contain C1+1, but not (C1+1)/8.
v The select list is applied to each group of R, and the result contains as many
rows as there are groups in R. When the select list is applied to a group of R,
that group is the source of the arguments of the aggregate functions in the select
list.
v The RRN, RID, DATAPARTITIONNAME, DATAPARTITIONNUM,
DBPARTITIONNAME, DBPARTITIONNUM, and HASHED_VALUE functions
cannot be specified in the select list.
If you think about what Group By does. It returns one record for each group of records where your group by fields are identical. So if you have 7 records where IARCC9 and IDDOCD are identical then your view will return one record. So, how does it know which one of the 7 records value of IDCOM# to return? It doesn't so you get the error. If you know all 7 records have the same value then you can have something like MAX(IDCOM#). It just depends on your database. You have to tell it what to return, it won't guess and give you one of those 7 values that it randomly chooses.
Scott Mildenberger
Programmer/Analyst
Davis Transport Inc.
Missoula, MT
406-728-5510 x128
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Hoteltravelfundotcom
Sent: Sunday, September 22, 2013 1:26 PM
To: Midrange Systems Technical Discussion
Subject: Group by issue
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?
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# FROM ASTDTA.OEINDLID INNER JOIN ASTDTA.ICPRTMIA ON ASTDTA.OEINDLID.IDPRT# = ASTDTA.ICPRTMIA.IAPRT# INNER JOIN ASTDTA.OEINHDIH ON ASTDTA.OEINDLID.IDIDC#=ASTDTA.OEINHDIH.IHIDC# INNER JOIN ASTDTA.ADRESSAD ON ASTDTA.ADRESSAD.ADENT#=ASTDTA.OEINHDIH.IHENT#
WHERE ASTDTA.ICPRTMIA.IARCC9 <> '' AND ASTDTA.OEINDLID.IDCOM#='001'
AND ASTDTA.ADRESSAD.ADSFX# = SUBSTR(ASTDTA.OEINDLID.IDGRC#,9,3 ) GROUP BY ASTDTA.ICPRTMIA.IARCC9, ASTDTA.OEINDLID.IDDOCD
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
