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 last published in February 2005

Dig Deeper on iSeries SQL commands and statements

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close