Re: SQL statement example request

Tim,
CTE is Common Table Expression - create a table on the fly that has the
information you need, then use that table in the actual query.

In Eric's example the WITH creates a summary table called Dtl with the
fields orderID and total. Then he uses that table in the actual query to
compare the summary results with the header file contents.

With
Dtl (orderID, total) as
( Select ord_id, sum(extd_amt)
From OrderDtl
Group by ord_id
)

Select Hdr.*, Dtl.total

From OrderHdr Hdr inner join

Dtl on (Hdr.ord_id = Dtl.orderID)
Where Hdr.total <> Dtl.total


HTH

Jim


On Thu, Jun 12, 2008 at 2:29 PM, Tim Gornall <tgornall@xxxxxxxxxx> wrote:

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



--
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.