|
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:expression, or be
If GROUP BY or HAVING is used
v Each column-name in the select list must identify a grouping
specified within an aggregate function, or be a correlated reference:not apply
- 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
additional operators to the expression. For example, if the groupingas many
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
rows as there are groups in R. When the select list is applied to agroup of R,
that group is the source of the arguments of the aggregate functions inthe select
list.group of records where your group by fields are identical. So if you have
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
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 Mildenbergermidrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Hoteltravelfundotcom
Programmer/Analyst
Davis Transport Inc.
Missoula, MT
406-728-5510 x128
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
Sent: Sunday, September 22, 2013 1:26 PMa way to fix this code so that it would be Group by?
To: Midrange Systems Technical Discussion
Subject: Group by issue
this view is fine without the Group by. But I really need that. Is there
Column IDCOM# or expression in SELECT list not valid.IDPRT#, IDGRC#, IDENT#, IDSFX#, IDPRLC, IDNTU$, IDSHP#,
CREATE VIEW ASTCCDTA.QTYUSAGE AS SELECT IARCC9, IDDOCD, IDCOM#, ADSFX#,
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'BY ASTDTA.ICPRTMIA.IARCC9, ASTDTA.OEINDLID.IDDOCD
AND ASTDTA.ADRESSAD.ADSFX# = SUBSTR(ASTDTA.OEINDLID.IDGRC#,9,3 ) GROUP
--list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-lmoment to review the archives at http://archive.midrange.com/midrange-l.
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
--
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 mailing list archive is Copyright 1997-2025 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.