Time for a Common Table Expression!

Hey, Howard:

Can you please help solve this SQL Problem? The following query returns the output shown below.

SELECT TDATE, LOCN, PRODUCT, 
       RETAIL, Sum(QTY) as TotalQty 
From MyFile 
Group by TDATE, LOCN, PRODUCT, RETAIL 
Order By TDATE, LOCN, PRODUCT, RETAIL DESC

The query runs okay if there is no refund, but if there is a refund, as you see in the last two lines, I want to convert these two lines to one line, like this:

How can I do this in the above SQL Statement?

--Shafiq

The solution is using a common table expression, which is kind of like a query over the results of a query. Take a look at this syntax:

SELECT TDATE, LOCN, PRODUCT, RETAILA, SUM(QTYA)
FROM 
  ( SELECT TDATE, LOCN, PRODUCT,
    ABS(RETAIL) as RETAILA,
    (CASE WHEN RETAIL<0 then -QTY else QTY END) as QTYA
  FROM MyFile
       ) AS X
GROUP BY TDATE, LOCN, PRODUCT, RETAILA
ORDER BY TDATE, LOCN, PRODUCT, RETAILA DESC

Let's take a look at what the above query is doing in order to better understand common table expressions. First, remember that SQL is like algebra: It works on parenthetical operations from the inside out. So, when looking at the above query, remember that the expression in the FROM clause of the query will be evaluated before the outer query syntax. The expression in the FROM clause tells the query to read the TDATE, LOCN, and PRODUCT fields and then to perform two functions against the RETAIL and the QTY fields. The first function uses the ABS function to return the absolute value of the RETAIL field. When RETAIL is negative, its absolute value is returned using the ABS function. If RETAIL is not negative, it returns the RETAIL field unmodified. In this manner, we are transforming retail into a number that is always positive. The second function is a CASE statement that looks at the value of the RETAIL field; if it is less than zero, it returns the negation of the QTY field; otherwise, it returns the QTY field unmodified.

If you were to run this statement by itself, it would return a set of records where the quantities are negated for refunds and where retail is a positive number. But since we have used the statement as an expression in the FROM clause of a containing statement, SQL will run this statement and create a virtual table called X, which is then processed by the containing statement. The container instructs SQL to group by the TDATE, LOCN, PRODUCT, and RETAILA fields and to SUM the QTY field, producing output like the following:

Common table expressions are really powerful and can be used as quick replacements for creating SQL views and creating temporary result sets that you can query.

Howard F. Arner, Jr., is vice president of Client Server Development and author of iSeries and AS/400 SQL at Work. To purchase Howard's book, go to www.sqlthing.com. E-mail: harner@itjungle.com.

Sponsored By WORKSRIGHT SOFTWARE

600 Billion That's how much a recent independent study estimated U.S. businesses spend on dirty data. How much of that 600 billion is spent by your company? Cleanse you dirty ZIP Codes and mailing addresses with our software and save big bucks. WorksRight Software, Inc. Phone: 601-856-8337 E-mail: software@worksright.com Web site: www.worksright.com