Right Syntax, Wrong Result: How SQL Can Go Awry

Esperant's SQL Expert is designed to prevent you from making choices that, though syntactically correct, violate the underlying logic of the query, producing inaccurate reports. Software AG offers the following scenario to illustrate how such a mistake can happen.

Start with a database that consists of the following three tables for customer name, orders, and order detail:

---

Creating a Query

illustration_link (16 Kbytes)

To create a query that returns each customer's total number of orders and order dollars, most query tools would allow a point- and-click query that generates the underlying SQL SELECT statement below. The database would perform the required joins to construct the following (undisplayed) table:

---

Resulting Table

illustration_link (9 Kbytes)

   SELECT T1.CNAME,SUM(T2.ORDER$),
       SUM(T3.QTY)
    FROM CUSTOMER T1, ORDER T2,
       ORDER_DETAIL T3
    WHERE T1.CUST# = T2.CUST#
      AND T2.ORD# = T3.ORD#
    GROUP BY T1.CNAME

---

Summing

illustration_link (5 Kbytes)

SQL would then do the summing against the internal table, displaying this report. The SUM(ORDER$) results, though they look fine at first glance, are actually incorrect. That's because ORDER$ has been duplicated for each Order Detail record, causing it to be overstated when the summing is performed. Accor ding to Software AG, only a user with a good grasp of SQL theory would have known that summing at two levels of detail in the same SELECT statement would produce erroneous results. Unlike most query tools, Esperant's SQL Expert would have grayed out the QTY column the instant you chose to SUM the ORDER$ column. The query would produce the following SQL code along with the correct report table:

---

Esperant's Expert Solution

illustration_link (5 Kbytes)

   SELECT T1.CNAME, SUM(T2.ORDER$)
    FROM CUSTOMER T1, ORDER T2
    WHERE T1.CUST# = T2.CUST#
    GROUP BY T1.CNAME