Examples2

www.cozTools.com

www.MidrangeNews.com

Contact Us

SQL iQuery

SQL iQuery™ - Examples

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

STRIQRY

For additional information, please visit these links:

To get started, the RUNIQRYF (Run ad hoc iQuery on a File) can be used to run a simple, ad hoc quiry over any file without SQL knowledge. It generates a dynamic SQL SELECT * FROM xxxxx statement, where xxxxx is the name of the file specified on the RUNIQRYF command.

For SQL users, the RUNIQRY (Run SQL using iQuery) command may be used to specify any SQL statement. It allows users to specify the SELECT, INSERT, UPDATE, DELETE, CREATE, etc. statements and processes them dynamically. Jump to the RUNIQRY section of this web page now.

In the following example, I'm using a classic "CUSTMAST" database file:

RUNIQRYF custmast

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

RUNIQRYF custmast OUTPUT(*PRINT)

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

RUNIQRYF custmast OUTPUT(*PDF)

The RUNIQRY 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 iQuery is right for you. If need the full power of pure SQL, check out RUNIQRY's companion, the Advanced RUNIQRY command here.

Customizing the RUNIQRYF Command

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

RUNQRYF Prompted

You can simply type in the file name and go! But RUNIQRYF 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:

RUNIQRYF custmast OUTPUT(*PRINT)

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

RUNIQRYF 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.

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

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

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

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

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

RUNIQRYF 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 iQuery's RUNIQRY 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. RUNiQRYF allows us to do that easily.

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

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

Sometimes a legacy RPGIII or early RPG IV programs need the results of an ancient SORT or the ever-popular OPNQRYF command. Using RUNIQRYF, 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:

RUNIQRYF 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, RUNiQRYF 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.

RUNiQRYF 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 RUNiQRYF with no ORDERBY parameter, and then run the same RUNiQRYF command with a sorted field. This helps make the two values meanings very clear.

RUNIQRY (Run SQL using iQuery) Command

The simplicity of RUNIQRYF is great for quick reports, inquiries, and for end-user tasks, but what about more complex situations? That's where the SQL iQuery RUNIQRY command comes in. Our RUNIQRY 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 RUNIQRYF, RUNIQRY 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 RUNIQRY command and the SQL syntax verses the CL-command driven syntax of RUNIQRYF command. Using the RUNIQRY command you can easily use SQL to query a few fields from a database table, for example:

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

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

SQL iQuery against a Local or Remote Database.

Just like RUNIQRYF, the RUNIQRY 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 RUNIQRY command, as follows:

RUNIQRY 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!

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

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

You may also want to check out the SQL iQuery Scripting Tool. It allows custom SQL Scripts to be run from source file members or IFS text files.

Copyright © 2014 Cozzi Productions, Inc. All Rights Reserved.