I am rarely asked to create reports anymore. More frequently I am asked to create extracts of data into output files. The users then upload the data from the output files into Microsoft's Excel, and "slice-and-dice" the data to generate the format they desire.
The traditional approach works well. The programmer creates:
- An output file to contain the extracted data.
- A program, usually RPGLE/RPG IV to extract the desired data from the input files.
In this post I am going to show an example, of what I consider, a simpler way to extract data from the input files and create an output file "on the fly", i.e. without a pre-existing output file, using SQL.
In this simple example I have a request to create an extract from the file INFILE. I can add the following into a SQLRPGLE member.
/free 01 exec sql CREATE TABLE QTEMP/OUTFILE AS 02 (SELECT SOURCE,ORDNO,CUSNO,PART,SPEC,ORQTY,DUEDT 03 FROM INFILE 04 WHERE SOURCE = '04') 05 WITH DATA ;
All of you familiar with SQL will recognize CREATE TABLE. I am creating the table (file) in QTEMP, as once the output file has been sent I have no longer need for it and it will be deleted when the job completes.
The SELECT statement on lines 2 - 4 gather the appropriate records from the input file.
The WITH DATA on line 5 is the critical part. This is informs the SQL processor that the data selected by the SELECT should be inserted into the output file.
When this completes I have an output file, and all of the columns (fields) in the output file have the same attributes as the fields in INFILE.
I prefer using this as I do not have to have a pre-existing output file that would have to be copied from one library to QTEMP before I could use it. The empty output file would take up space in a library. By all means one empty file does not take up much room, but hundreds start to.
If wanted to create an empty table I would use the DEFINITION ONLY in place of the WITH DATA, see below.
/free 01 exec sql CREATE TABLE QTEMP/OUTFILE AS 02 (SELECT SOURCE,ORDNO,CUSNO,PART,SPEC,ORQTY,DUEDT 03 FROM INFILE) 04 DEFINITION ONLY ;
I have to admit I am yet to find a reason to use the DEFINITION ONLY.
You can learn more about the CREATE TABLE SQL statement on IBM's website here.
This article was written for IBM i 7.1, and it should work with earlier releases too.