RE: Group by issue -- MIDRANGE-L

For reduced run time I usually start by attempting to
build the SQL WHERE clause to match an existing unique
index/logical file.

If I don't find an exact match, I try to build the
WHERE clause to match an access path that omits
a large portion of rows.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Hoteltravelfundotcom
Sent: Tuesday, September 24, 2013 6:47 AM
To: Midrange Systems Technical Discussion
Subject: Re: Group by issue

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.

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