RE: SQL: how to join header and detail records

["Stone, Joel" <Joel.Stone@xxxxxxxxxx>]

Yes I agree MAX/MIN would work, so would AVG.  But it just doesn't seem very self-documenting.  If someone else were to look at it a year later, they may be steered down the thought path that the developer was trying to do something other than use the simple column value.



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Schutte
Sent: Wednesday, April 03, 2013 12:21 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL: how to join header and detail records

You would either need to MAX/MIN totqty or put it in the GROUP BY.

I would MIN or MAX, it wouldn't matter because it would return the same
value every time.


On Wed, Apr 3, 2013 at 1:09 PM, <rob@xxxxxxxxx> wrote:

> The magic word is HAVING.
> HAVING is to GROUP as ... well, not really a good comparison.  Just let me
> explain it to you
>
> Select orderno, totqty, sum(dtlqty) as dtlsum
> from orderheader join orderdetail using orderno
> group by orderno
> having totqty <> dtlsum
>
> Now, you may have to say
> <> sum(dtlqty)
> but you should get the concept from that.
>
>
> Rob Berendt
> --
> IBM Certified System Administrator - IBM i 6.1
> 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
>
>
>
>
>
> From:   "Stone, Joel" <Joel.Stone@xxxxxxxxxx>
> To:     "'Midrange Systems Technical Discussion'"
> <midrange-l@xxxxxxxxxxxx>,
> Date:   04/03/2013 12:59 PM
> Subject:        SQL: how to join header and detail records
> Sent by:        midrange-l-bounces@xxxxxxxxxxxx
>
>
>
> I have an order header and detail file with one to many.
>
> The order header has TOTQTY, which is the sum of all detail quantities.
>
> The order detail has DTLQTY, which is one line's qty.
>
>
>
> If I do a join of the two files and there is one HDR and five DTL records,
> there will now be FIVE rows.
>
> I want to compare the sum of the DTLQTY to the single TOTQTY value.
>
> I can use SUM(DTLQTY) to provide the sum of the detail.
>
> What is a good way to provide the TOTQTY?  I don't want to use SUM(TOTQTY)
> as this would give a result 5 times the correct value.
>
> Is MAX(TOTQTY) what people generally use?
>
> But that seems to be misleading since I am not really looking for the max
> value, as all rows contain the same value for a header column.
>
> Should I be grouping the detail PRIOR to the join, so there is only ONE
> header value?
>
> But it seems like the SQL stmt is SO much simpler if I do the join first.
>
> Any ideas?
>
> Thanks!
>
>
>
> ______________________________________________________________________
> This outbound email has been scanned for all viruses by the MessageLabs
> Skyscan service.
> For more information please visit http://www.symanteccloud.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.
>
>
> --
> 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.