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? 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:

to an output file, use the following INSERT statement:

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.

• Running multiple SQL statements


• Use SQL to SELECT data directly to a CSV file


• Poor response time when running a SQL select statement

To create a file suitable for receiving the output from the above example, the following CREATE TABLE statement can be used:

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT iSeries administrator tips Researching high availability for your System i shop Translating Linux for IBM i admins: Using GUI to make it easy Translating Linux for IBM i admins: Working with jobs and networking OpenOffice: What to know before making the transition from Microsoft Office OpenOffice: An enterprise open source solution Database performance comparisons on IBM i Translating Linux for IBM i admins: User profile commands Modern System i reports using Client Access Tips for installing Lotus Domino server on a System i partition The iSeries Blog has a new home on IT Knowledge Exchange iSeries SQL commands and statements Enhancing RPG with external SQL stored procedures Tracking data changes on IBM i with triggers Introduction to SQLRPGLE on IBM i: Making a report Making the most of RPG data handling on IBM i When is the YES option for 'reuse deleted files' function the best choice? Monitoring members 'stuck' within a physical file on an EDI system Creating a host variable of the 'where in' statement in SQL SQL server error message -321 Choose which column names are returned via ODBC when working with DB2 files Convert a numeric physical file to a character in SQL without leading zeros iSeries SQL commands and statements Research

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

font-family:"Trebuchet MS"; panose-1:0 2 11 6 3 2 2 2 2 2; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:50331648 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Trebuchet MS";} p.code, li.code, div.code {mso-style-name:code; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:12.0pt; margin-left:0in; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Courier New";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->

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

Alternatively, you can create an outfile with just a single character field long enough to hold the concatenation of all the fields in the select statement:

Then, modify the select statement to concatenate (i.e., attach one string to the end of another) all the fields together:

The concatenation operator (|| -- double bar) is used to attach one character field to another. The digits() function must be used on the zip field to convert the data from numeric to character.

To clear an outfile before running the INSERT statement (INSERT can only add records to a file), use the DELETE statement:

This is a simple DELETE statement where every row in file Cust_Out is deleted. Note: You can also use the DELETE statement to delete specific rows. Use the DROP statement to delete the entire file -- object and all.

-----------------------------------
About the author: Ron Turull is editor of Inside Version 5. He has more than 20 years' experience programming for and managing AS/400-iSeries systems.