RE: Need a little help with SQL summary syntax

You can't use as alias in a Group By clause, and you shouldn't include
column functions in the group by clause as they are the results of the
grouping.

Your group by clause should be:
GROUP BY
a.qezdiridx, qezdirnam1, qezdirlen, qezpardir

Tom Armbruster
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Roger Vicker, CCP
Sent: Tuesday, March 25, 2008 8:06 AM
To: RPG400
Subject: Need a little help with SQL summary syntax

I thought I had this SQL statement all figured out. But, when I run it I

get an error that "Column NUMOBJS not in specified tables." NUMOBJS is
in SUMDIR01P. There are two more columns in SUMDIR01P that will be
filled in by a subsequent process. SUMDIR01P has 8 columns in total and
I am only trying to fill 6 of them with this statement.

INSERT INTO
sumdir01p
(qezdiridx, qezdirnam1, qezdirlen, qezpardir, qezalcsize, numobjs)
SELECT
a.qezdiridx, qezdirnam1, qezdirlen, qezpardir,
COALESCE(SUM(b.qezalcsize), 0) AS qezalcsize, COALESCE(COUNT(*), 0) AS
numobjs
FROM
dirinf/QAEZD0037d A LEFT OUTER JOIN dirinf/QAEZD0037o B
ON
a.qezdiridx = b.qezdiridx AND qezobjtype NOT IN ('*DIR', '*DDIR',
'*FLR')
GROUP BY
a.qezdiridx, qezdirnam1, qezdirlen, qezpardir, qezalcsize, numobjs
ORDER BY
qezdirnam1

Anything that I can think of for NUMOBJS, I think would cause the same
problem for QEZALCSIZ.

Please advise on what I haven't been able to find in the books to make
this work.

Roger Vicker, CCP