Re: SQL statement example request

Tim Gornall wrote:

Calling all SQL gurus, I wanted to do an adhoc report using SQL earlier today. I only know the
very basics when it comes to SQL, but I what to start digging deeper so to
speak. I ended up banging it out in RPG. I would like to see how you guys
would have done it with a SQL statement.
I have a header file with a total field. This total should match the sum of
the transactions in a detail file. I needed to list all header records that
did not match, displaying the header amount and the sum of the detail.
Sounds easy, eh? And it may be. Just in case someone has nothing better to
do...

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