Work Fields and SQL

Published: February 15, 2006

Hey, Ted:

I haven't been able to get SQL to handle result fields in the same way Query/400 can. In Query, I can define a result field, then use that field in the definition of another field. If I try this with SQL, I get a message that says, "Column X not in specified tables." Is there a way to avoid duplicating the calculations of a work field?

--Erik

No, Erik. Not with SQL/400. To my knowledge, the only SQL-based software product that permits you to define work fields is Advanced System Concepts' SEQUEL. But I can give you some workarounds that should work with most any version of SQL.

First, let's define the problem for the other readers. Erik sent me the following Query result field definitions.

Field  Expression           Heading        Length
=====  ==================== ========       ======
X      T01.DUR              minute            3 0
Y      (T01.DUR-X)*100      seconds           2 0
Z      X * 60 + Y           time in seconds   5 0
TID    Z * T01.COST

DUR (duration) is a five-digit numeric field with two decimal positions. The whole-number portion of DUR indicates the number of minutes, and the fractional portion indicates seconds.

Eric tried to run something like this:

SELECT DEC(t01.DUR,3,0) AS X, 
       DEC((t01.DUR-X)*100,2,0) AS Y,
       DEC(X * 60 + Y,5,0) AS Z,
       Z * t01.COST as TID
  FROM whatever AS t01

But SQL responded with the message he mentioned because there is no column (field) named X in the table he was querying.

One way to handle calculations of this type is to repeat the expressions, like this:

SELECT DEC((DEC(t01.DUR,3,0)) * 60 +
       (DEC((t01.DUR-(DEC(t01.dur,3,0)))*100,2,0)),5,0)
        * t01.COST AS TID
  FROM whatever AS t01

Isn't that messy? I'm not even sure I pasted it correctly. It's a direct translation from Query, which is a mistake, for the simple reason that SQL is more powerful than Query. The way to make the query better is to use SQL's built-in functions, which are more numerous than those of Query.

Let's start from scratch and find a solution. The problem is to convert DUR into seconds, then multiply the COST to get some other field we'll call TID. Here's my first effort.

SELECT ((INT(t01.DUR) * 60) +
        ((t01.DUR - INT(t01.DUR)) * 100))
        * cost as TID
   FROM elapsed AS t01;

Here's another effort that looks a little less messy.

SELECT (INT(t01.DUR) * 60 + mod(t01.DUR * 100,100))
        * t01.COST as TID
  FROM elapsed AS t01

The point is that you can probably get along more easily without the work fields by rethinking the problem and addressing it with SQL functions.

Speaking of functions, here's something else SQL can do that Query can't. SQL lets you define your own functions. If converting 5,2 duration fields to seconds is something you do a lot of, why not create your own conversion function?

CREATE function mylib/ToSeconds                    
 (inTime DEC (5,2))                                 
 returns DEC (5,0)                                  
 LANGUAGE SQL                                       
 reads SQL DATA                                     
 NO EXTERNAL ACTION                                 
 BEGIN                                              
   return INT(inTime) * 60 + mod(inTime * 100,100); 
 END

Now that you have a function, the query couldn't be easier.

SELECT toseconds(t01.dur) * t01.cost as TID
  FROM elapsed AS t01

Expressions also get repeated in other places within SQL queries. For instance, you may sort on expressions, like this:

SELECT order, line, item, qty, price, 
       MAX(entrydate, reldate)
  FROM OrderLines
 ORDER BY MAX(entrydate, reldate)

I don't like the repeated MAX function. Fortunately, there's a short cut you can use in the ORDER BY clause. If you like, you can use a number to indicate a sort field.

SELECT order, line, item, qty, price, 
       MAX(entrydate, reldate)
  FROM OrderLines
 ORDER BY 6

The number 6 in the ORDER BY clause means to sort on the sixth column.

You can't use numbers in the WHERE, GROUP BY, and HAVING clauses, so you must repeat the expressions in those places.

SELECT MAX(entrydate, reldate), COUNT(*)
  FROM OrderLines
 GROUP BY MAX(entrydate, reldate)
 ORDER BY MAX(entrydate, reldate)

Sometimes you can use a common table expression to avoid repetitious code.

WITH Temp AS 
   (SELECT order, line, item, qty, price, 
           qty * price AS Extended,
           MAX(entrydate, reldate) AS BaseDate
      FROM OrderLines)
SELECT BaseDate, Extended, Item, Order, Line, Qty
  FROM Temp
 WHERE Extended > 200
 ORDER BY 1, 2 DESC, 3, 4, 5

Temp is a temporary table, existing only while the query is running. Notice that there are two calculated fields--Extended and BaseDate. The Select uses the two fields as if they really existed in the database.

So, while it's true that SQL does not allow you to create intermediate result fields the way Query does, there are plenty of ways to get the required results. I hope this gives you some ideas.

--Ted