Write results of SQL Select statement to an outfile

Outfiles are status quo on the iSeries, but did you know that SQL can produce them intrinsically? Ron Turull shows you how.


Ron Turull

Outfiles are status quo on the iSeries, 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), specify the Select statement as part of an Insert statement. For example, to output the rows resulting from the following Select statement:

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

to an output file, use the following INSERT statement:

 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 exactly six fields in the order specified on the select statement. However, 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 from the above example, the following CREATE TABLE statement can be used:

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

 


 More Information in SQL statements

This was first published in February 2005

Dig deeper on iSeries SQL commands and statements

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchEnterpriseLinux

SearchDataCenter

Close