Work Fields and SQL
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?
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.