Re: SQL statement example request

["Tim Gornall" <tgornall@xxxxxxxxxx>]

Thanks Joe!  What is "CTE"?   


date: Thu, 12 Jun 2008 14:14:57 -0500
from: Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>
subject: Re: SQL statement example request

What gets weird here is the grouping.  Using basic SQL syntax, the 
easiest is this:

  select hdr.key1, hdr.total, sum(dtl.amount)
    from hdr join dtl on hdr.key1 = dtl.key1   
    group by hdr.key1, hdr.total               
    having hdr.total <> sum(dtl.amount)        
 

This assumes only one key field (such as order number) and it also 
assumes that there is only one header record per detail, thus allowing 
me to group by key1 and total.  The having clause then allows you to 
compare the total from the header to the summed amount from the detail.

There are probably better ways nowadays.  In fact, I would normally go 
with a CTE, and some of the fancy new SQL syntax might make it even 
easier.  But this will work and it's relatively easy to read.

Joe