× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



group by i thought to improve the run time. the IARCC9 has the customer
name there and we want to report according to that.


On Mon, Sep 23, 2013 at 11:05 AM, Vernon Hamberg
<vhamberg@xxxxxxxxxxxxxxx>wrote:

Nice explanation, Scott.

I'm going back to the OP's question - what are you trying to accomplish
with a GROUP BY? Do you need additional records with things like
subtotals or counts?

You say you need the GROUP BY - but not why, or what you hope to
accomplish.

Later
Vern

On 9/23/2013 9:40 AM, Scott Mildenberger wrote:
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.



--
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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.