Did you know that you can create work files in SQL very easily? This article shows you how.

In my previous article, I identified a class of applications in which SQL and RPG join forces through the magic of work files. These applications use SQL to access the data but still take advantage of ILE RPG to perform more in-depth database manipulation. The trick is creating the work file, and this article will show two separate ways to do that. Note that this article isn't specific to the work file application I described; you can use either of these techniques any time you need a work file, even just a temporary ad hoc work file for analysis or extract purposes.

The Business Requirement

Here's the business requirement: give me a list of all customers who have one or more orders in the past three months with lines underpriced by more than 10 percent. You could theoretically do this with a single massive SQL statement, but a lot depends on how difficult it is to determine the expected price. When all you have is a single standard price per item, it's relatively easy:

select distinct OHCUST                        

from ORDHDR join ORDDTL on ODORNO = OHORNO

             join ITMMST on IMITEM = ODITEM  

where ODITPR < (IMITPR * .90);              
order by OHCUST;              

There you have it, a list of customer numbers in customer number order that have at least one customer order line where the order price was less than 90 percent of the item's standard price. Although the field names might be relatively self-explanatory to those who read RPG, let me explain them a little more fully. There are three files:

If you're old-school RPG like me, you might recognize the basic database design approach. Each file has a six-character name, a description, and a two-character prefix. Every field in those files has a six-character name, the first two characters of which are the file's prefix. That leaves four characters to define the field, and we try to keep those consistent across files. Thus xxORNO is order number in every file, where xx is the file's prefix.

The order header identifies the customer order and has one record per order. This record identifies, among other things, the order number and the customer number. The order detail contains one record for every order on a line, including the item number and the unit price. Finally, the item master has one record for every item and is where we store the item price.

The SQL statement above would create the list of customers that have orders in which a line was underpaid by more than 10 percent. Pretty simple, but also not particularly useful. Think about what will probably happen: as soon as you give that to the users, they're going to want a list of the orders. Not only that, they're going to want a list of the offending order lines. And for context, I find that users typically end up wanting a list of all the lines for all the orders in which any line has one or more underpaid lines. And don't be surprised if they want it sorted by customer and order number. That's actually not too difficult (for now):

with UnderpaidOrders as (select ODORNO      

from ORDDTL join ITMMST on IMITEM = ODITEM            

where ODITPR < (IMITPR * .90))            

select * from ORDDTL where ODORNO in        

(select ODORNO from UnderpaidOrders)              

order by OHCUST, ODORNO;                  

This query uses a Common Table Expression (CTE) to create a temporary list named UnderpaidOrders of order numbers that have at least one line where the unit price is less than 90 percent of standard. We then select all the fields from every record in ORDDTL where the order number is in that list, and order it by customer and order number. This gives me a list of all the lines in every order where at least one line is underpriced.

Technique 1: Externally Described Files

This technique is probably the most familiar to RPG programmers. We start with an existing externally described file, which we then load with the SQL statement. In this case, we're selecting all the fields from the ORDDTL order detail file, using SELECT * FROM ORDDTL. This means we simply need a file with the same layout as ORDDTL. It's not hard to create a file in QTEMP using CRTDUPOBJ (with the caveat that you have to get a lock on the object; for production files that traditionally means having a template file with the same layout). Run the SQL statement to load the file and then process the selected records using the technique we learned in the previous article:

     dcl-f ORDDTL extfile('QTEMP/ORDDTL');

The ORDER BY clause in the SELECT statement ensures that the records are placed in the file in proper order and your program can just process the file with a traditional read. If you wanted to go really old-school, you could even use the RPG cycle and level breaks; how's that for merging the old world and the new? For those of you who are cringing at my bringing up the cycle, let's move on to the more up-to-date processing style.

Technique 2: The All-SQL Solution

To do this, we need to take advantage of embedded SQL. At that point, we're no longer beholden to file layouts and we use a bit more sophistication. Let's assume, for example, that we only want a few specific fields from the order detail file. In addition, we'd like to include some header information in our query as well. We could of course go out and chain for the header data as we need it, but there are design conditions under which you might want that data replicated in the result file. For example, the query we're writing might print allow the user to select either a traditional RPG printer file report or alternatively export to another format such as XML or CSV. By creating the work file with all the needed fields, the export is a very simple step.

Whatever your reasoning, there may be times when you simply don't have that predefined externally described work file. In that case, we can go to a full SQL solution. These lines will be included in your embedded RPG program, so remember that they will be preceded by the EXEC SQL opcode. For example, our first task is to delete any existing work file already in QTEMP. Use the DROP statement:

exec sql drop table QTEMP.Underpaid;

That's all it takes; the table UNDERPAID in QTEMP will be dropped. You might notice that I used dot (.) syntax rather than slash (/) syntax. This has been available since 7.1, and I highly recommend it; it means you can drop the same SQL statement into any available SQL client, whether it's IBM i Navigator or SQuirreL SQL. I also deleted the table named UNDERPAID. In a moment, I'll create that same table with data.

OK, on to the next case. Let me expand the SQL statement to get the order header information and also to create the temporary file. It's really not hard.

exec sql                                      

create table QTEMP.Underpaid as            

(with UnderpaidOrders as (select distinct ODORNO    

   from ORDDTL join ITMMST on IMITEM = ODITEM            

   where ODITPR < (IMITPR * .90))            

select OHORNO, OHCUST, OHORDT,              

         ODORLN, ODITEM, ODITPR, ODORQT, IMITPR

from ORDDTL join OHCUST on ODORNO = OHORNO

             join ITMMST on IMITEM = ODITEM            

   where ODORNO in (select OHORNO from UnderpaidOrders)              

   order by OHCUST, ODORNO))
with data;                

Some of the statement is familiar; the CTE stays the same, creating a list of all order numbers having at least one underpaid line. Then we have two significant differences. The first is that the query selects specific fields, and it gets those fields from all three files (ORDHDR, ORDDTL, and ITMMST). It still uses the CTE to select only the records it wants, but the fields are very specific. The real work, though, is in the CREATE TABLE statement. The syntax is very simple, but it's extraordinarily powerful: this statement creates a table named UNDERPAID in QSYS that contains the selected fields, and fills it with the rows that meet my selection criteria (in this case, being part of an order with at least one underpaid line).

One important point: since the name is 10 characters or less, it will be created with that as its QSYS name and as its SQL name. If instead I used the table name UnderpaidOrders, I'd end up with a QSYS name of UNDER00001. Since this is a work file intended only to be used in SQL, that might not be an issue for you, but don't be surprised if you see that odd name if you execute a DSPLIB.

Now technically this is an externally described file like any other, and RPG programs could be compiled over it. However, the file would have to exist before you could compile the program, and any time you changed the field list you'd have to change that file: all the stuff you're used to dealing with today. If instead, though, you simply used embedded SQL and a cursor, you could simply read through the newly created file. Another article will detail that technique. For now, though, you can easily use this to create any work files you might need.