---

Return to the SQL Tips

---

How to have the results of a Select statement written to an outfile.

Outfiles are the status quo on the AS/400, but did you know that SQL can produce them intrinsically? To have the results of a SELECT statement written to a database file (i.e., an outfile), embed an SQL SELECT statement in an INSERT statement. For example, to output the rows resulting from:

Select name, addr1, addr2, city, state, zip from cust_mast where state = 'IL' order by zip

to an output file, use:

Insert into library/outfile select name, addr1, addr2, city, state, zip from cust_mast where state = 'IL' order by zip

The library and outfile names you specify must already exist, and the file must have the correct number of fields (6 for the above example), and the field must be in the same order as specified on the select statement (note, the fields in the outfile do not have to have the same name as the ones on the select statement but they must have compatible attributes).

To create a file suitable for receiving the output for this example, you can use DDS or the following SQL CREATE TABLE statement:

Create Table UCG/Cust_Out 
( name char(30),
addr1 char(30),
addr2 char(30),
city char(20),
state char(2),
zip dec(5,0)
)

Making the results importable to other databases.

Alternatively, you can create an outfile with a just a single character field long enough to hold the concatenation of all the fields in the select statement. This could be used, for instance, if you are going to import the resulting file into a non-AS/400 database.

For example, many databases allow you to import data in which the fields are comma-delimited. To do this for the above example, use the following SQL command to create a file with a single field definition:

Create Table UCG/Cust_Out 
( rec char(122) ) 

Then, modify the select statement to concatenate (i.e., attach one string to the end of another) all the fields together, with each field delimited by a comma:

Insert into UCG/Cust_Out
select 
name || ',' || addr1 || ',' || 
addr2 || ',' || city || ',' || state || ',' || digits(zip)
from cust_mast where state = 'IL' order by zip

The concatenation operator (||) is used to attach one character string to another. The digits function must be used on the zip field to convert the data from numeric to character (digits is one of many special SQL functions). To clear an outfile before running the INSERT statement (INSERT can only add records to a file, not replace them), use the SQL DELETE statement:

Delete from UCG/Cust_Out

This is a simple DELETE statement where every row (i.e., record) in file Cust_Out is deleted. Note, you can also use the DELETE statement to delete specific rows. You can also use the CLRPFM OS/400 command.
To delete the file itself, you can use either the SQL DROP statement or the Delete File (DLTF) OS/400 command.

[report a broken link by clicking here]

---