Re: Multiple counts when joining files

[Luis Rodriguez <luisro58@xxxxxxxxx>]

Rob,

Are you getting a syntax error? Also, I'm supposing that DIOBLI is in both
tables (as per the USING clause). Shouldn't you be qualifying it elsewhere?

Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--



On Tue, Apr 19, 2011 at 3:11 PM, <rob@xxxxxxxxx> wrote:

> I can't seem to get this right:
>
> SELECT DIOBLI, SUM(HQ.DIOBSZ) AS HQSIZE,
>               COUNT(HQ          ) AS HQNBROBJ,
>               SUM(HQ2.DIOBSZ) AS HQ2SIZE,
>               COUNT(HQ2          ) AS HQ2NBROBJ,
>               MAX(HQ.DICDAT) AS CHANGEDDATE
> FROM ROB/GDIHQOBJ HQ LEFT OUTER JOIN ROB/GDIHQ2OBJ HQ2
>     USING (DIOBLI, DIOBNM, DIOBTP)
> WHERE DIOBLI<>' ' AND DIOBLI <>'QDOC'
> GROUP BY DIOBLI
> ORDER BY DIOBLI
>
>
> Rob Berendt
> --
> Group Dekko
> Dept 1600
> Mail to:  2505 Dekko Drive
>          Garrett, IN 46738
> Ship to:  Dock 108
>          6928N 400E
>          Kendallville, IN 46755
> http://www.dekko.com
>
> --
> 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.