RE: sql question

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

Use a HAVING clause to compare the sum(detail$$) to the hdr_total$$ values.

Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of tim
Sent: Monday, December 10, 2007 3:24 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: sql question


Worked perfect with where clause...

To go one step furher. I would like to compare the order header total with
the order detail and display only those that are not equal.

I tried adding where amount = damount, but got message damount not found.

I defined damount by (select sum(amount) from detail) as damout



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of vhamberg@xxxxxxxxxxx
Sent: Monday, December 10, 2007 4:02 PM
To: Midrange Systems Technical Discussion
Subject: Re: sql question

Guy

You need some correlation between the orderfile and orderdetail - something
like

        where orderdetail.orderno = orderfile.orderno

in the subquery. Your version will give the same sum for everything, I
believe. ;-)

Rob mentions a left outer join, which would give results when there is no
detail - that may or may not be desired. Your version, with the correlation,
might work like a left outer join - not sure - or might return a NULL in
that case - that'd be a great time to use the COALESCE function, to give it
a 0 and not a NULL. 

HTH
Vern

 -------------- Original message ----------------------
From: GUY_HENZA@xxxxxxxxxxxxxx
> Yes.
>
> Select orderno, orderamt, (Select sum(orderamt) from orderdetail) from
> orderfile
>
> Regards,
>
> Guy

>                       "tim"

>                       <tim2006@xxxxxxxxxxx        To:       "'Midrange
Forumn'" 
> <midrange-l@xxxxxxxxxxxx>                                 
>                       >                           cc:

>                       Sent by:                    Subject:  sql question

>                       midrange-l-bounces@m

>                       idrange.com

>

>                       12/10/2007 03:03 PM

>                       Please respond to

>                       Midrange Systems

>                       Technical Discussion    
>
> I wish to do this in 1 sql statement not not sure if I can.
>
> Select orderno, orderamt from orderfile
>
> Select sum(orderamt) from orderdetail
>
> Resulting in:
>
>             Order no, order amount, sum of order amounts from detail file.
>
> Can this be done in 1 sql?