RE: sql question

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?