SQL Query File™ - Examples

After restoring SQL Query File onto your IBM i system, go to the SQL Query File menu, as follows:

STRQRYF

For additional information, please visit these links:

To get started, run a simple interactive inquiry over any file. In the following example, I'm using a classic "CUSTMAST" database file:

RUNQRYF custmast

If you prefer a printed report of the contents of the file, that's just as easy:

RUNQRYF custmast OUTPUT(*PRINT)

If you need a PDF report so the results can be emailed, again, that's just as easy:

RUNQRYF custmast OUTPUT(*PDF)

The RUNQRYF command is an easy-to-use interface that allows end-users and programmers to run SQL queries over database files. Whether they know very little (or no) SQL or are a full blown DBA, SQL Query File is right for you. If need the full power of pure SQL, check out RUNQRYF's companion, the Advanced RUNSQLF command here.

Customizing the RUNQRYF Command

When prompted, RUNQRYF options may be customized. Considering the simple nature of the command, this is a very powerful feature.

You can simply type in the file name and go! But RUNQRYF also allows users to select the fields and records to be included, and sequence the results. In addition, the results may be output to the display, print (SPOOL) file, database file, PDF file or a plain text file on the IFS.

Here are a few more examples:

Create a Report/Listing (SPOOL File) of the data in a file on your library list:

RUNQRYF custmast OUTPUT(*PRINT)

Create a PDF of the data in a file on your library list and store it in your HOME directory:

RUNQRYF custmast OUTPUT(*PDF) /* Requires V6R1 or later */

Create a Report of the Customer Number, Name and EMAIL Address only:

The FLD (list of fields to include) parameter is a standard command "list of things" parameter. Specify the field names to include as illustrated here.

RUNQRYF custmast FLD(CUSTNO CUSTNAME EMAIL) OUTPUT(*PRINT)

Create the same report as above, but sort the results by CUSTNAME:

RUNQRYF custmast FLD(CUSTNO CUSTNAME EMAIL) OUTPUT(*PRINT) ORDERBY(custname)

Unlike the FLD parameter, the ORDERBY parameter accepts the same syntax as the SQL ORDER BY clause. This provides the most flexibility and simplicity when sequencing your results. For example, to sort the results by Company name and then email within Company, you would specify the following:

RUNQRYF custmast FLD(CUSTNO CUSTNAME EMAIL) OUTPUT(*PRINT) ORDERBY('company,email')

Note that the ORDERBY along with the WHERE parameters, require the value to be enclosed in quotes. The values specified on these two parameters is passed directly to the SQL processor.

Create the same sorted report as above, but include only customer numbers greater than 2100 and dump the results into a CSV file on the IFS:

RUNQRYF custmast FLD(CUSTNO CUSTNAME EMAIL) OUTPUT(*CSV) 
			ORDERBY('company,email') WHERE('custno > 2100')

Query the CUSTMAST file that exists on the remote system named CHICAGO, and view the results:

RUNQRYF custmast FLD(CUSTNO CUSTNAME EMAIL) RDB(CHICAGO) USER(MYUSER) PWD(ROSEBUD)

The need to access database files on other partitions or systems is pervasive. SQL Query File's RUNQRYF and QRYF commands allow you to run the same inquiry on your local partition or a remote system. In our environment we have over a half dozen partitions, including disaster recovery, test and web server. Sometimes we want to view the contents of another partition to verify that the data is what we think it should be. RUNQRYF allows us to do that easily.

Copy a subset of the CUSTMAST file to a new file named WORK1 in QGPL:

RUNQRYF FILE(CUSTMAST) OUTPUT(*FILE) FLD(CUSTNO CUSTNAME EMAIL) OUTFILE(QGPL/WORK1)

Sometimes a legacy RPGIII or early RPG IV program needs the results of an ancient SORT or the ever-popular OPNQRYF command. Using RUNQRYF, you can direct the results of your query to another database file and then use that file in the RPGIII or RPG IV (or CL) programs. Then when you're finished with it, just clean it up if necessary.

Include the Row ID or Relative Record Number in the Results:

RUNQRYF myDataLib/custmast FLD(*ROWID)

Sometimes we need the line number (sequence number of the query results) included with the output. By including the special value *ROWID on the FLD parameter, RUNQRYF includes the row number in the output. The *ROWID may be specified by itself (to include all fields and the row number) or with other field names.

RUNQRYF myDataLib/custmast FLD(*RRN)

The Relative Record Number (RRN) differs from the Row Number (ROWID) in the following way: The RRN is the actual relative record number of the record in the database file. The ROWID is an SQL runtime row number as it appears in the results; similar to a line number. Use *RRN to include the relative record number in the results.

You can also use both *RRN and *ROWID in the results to compare the two in different situations. For example, issue a RUNQRYF with no ORDERBY parameter, and then run the same RUNQRYF command with a sorted field. This helps make the two values meanings very clear.

RUNSQLF (Run SQL using Query File) Command

The simplicity of RUNQRYF is great for quick and easy reports, inquiries, and for end-user tasks, but what about more complex situations? That's where the SQL Query File RUNSQLF command comes in. Our RUNSQLF command opens up the SQL language to Developers or Sophisticated Users who need the power of pure SQL to produce a report or inquiry. Unlike RUNQRYF, RUNSQLF command accepts a full SQL statement, allows customization of SQL options like date format, naming, and more, and supports runtime substitution variables (when embedded in CL). SQL-savvy users and Programmers often prefer the RUNSQLF command and the SQL syntax verses the CL-command driven syntax of RUNQRYF command. Using the RUNSQLF command you can easily use SQL to query a few fields from a database file, for example:

RUNSQLF 'select custno,custname,email 
       FROM custmast 
       WHERE CUSTNO BETWEEN 100 and 500 
       ORDER BY company,email'

Unlike the RUNQRYF command, the RUNSQLF command allows you to specify a pure SQL statement. All valid IBM DB2 SQL is supported.

SQL Query File against a Local or Remote Database.

Just like RUNQRYF, the RUNSQLF command allows you to run an SQL statement on the local partition or access a remote partition or system. To produce an interactive query over the CUSTMAST file that is stored on the remote CHICAGO system, add the RDB, USER and PWD parameters to the RUNSQLF command, as follows:

RUNSQLF SQL('select custno,custname,email 
       FROM custmast 
       WHERE CUSTNO BETWEEN 100 and 500 
       ORDER BY company,email')
     RDB(CHICAGO) USER(MYUSERID) PWD(ROSEBUD)

The results are pulled onto the local system from the remote system and displayed or printed locally. It is that easy!

RUNSQLF and 3-Level SQL Names

You may also use 3-level names and avoid the RDB/USER/PWD parameters entirely. To use 3-level names, specify NAMING(*SQL) and then fully qualify the database table names to the remote system name and library, as follows:

RUNSQLF SQL('select custno,custname,email 
       FROM chicago.mylib.custmast 
       WHERE CUSTNO BETWEEN 100 and 500 
       ORDER BY company,email')
     NAMING(*SQL)