INFO

www.cozTools.com

www.MidrangeNews.com

Contact Us

COZTOOLS

SQL Query File™ for IBM i

Get to where you need to be

The power of SQL Reports with the simplicity of a CL command, that's SQL Query File.

For a list of our latest enhancements, fixes, and updates, check out our What's new in SQL Query File log.

Use SQL Query File to produce classic columnar reports, inquiries or result sets. Simply create the query you want using SQL or our CL command interface, then direct the output to any of a growing list of output media. Need to display a list of Account Codes? No problem. Want to send the Account Code list to a user via PDF format? Just change the OUTPUT parameter to *PDF.

SQL Query File's two primary commands RUNQRYF and RUNSQLF allow users of all levels to leverage database query on IBM i.

  • RUNQRYF command allows users to quickly query a file and display or print the results.
  • RUNSQLF command is for those times when you want full customization of the output, and need to use pure SQL rather than CL parameters.

In addition our powerful and user friendly WRKQRYF command allows users to prompt the list of fields and build their query (SQL statement) using familiar IBM i selection panels.

How Easy is SQL Query File?

RUNQRYF CUSTOMER

That's it. To create an ad hoc report, display or outfile, of the CUSTOMER file, that's all there is to it. When to include only the customer number, name and email address sorted by name?

RUNQRYF CUSTOMER FLD(CSTNBR CSTNAM EMAIL) ORDERBY(CSTNAM)

It is that easy!

Want to include only the customers in CHICAGO?

RUNQRYF CUSTOMER FLD(CSTNBR CSTNAM EMAIL) WHERE('CSTCTY = ''CHICAGO''') ORDERBY(CSTNAM)

Want to take that result and send it to the IFS as a PDF? No problem:

RUNQRYF CUSTOMER FLD(CSTNBR CSTNAM EMAIL) WHERE('CSTCTY = ''CHICAGO''') ORDERBY(CSTNAM) OUTPUT(*PDF)

A PDF is created in the User's home directory.

Want to generate the equivalent RUNSQLF command and corresponding SQL statement in the joblog?

RUNQRYF CUSTOMER FLD(CSTNBR CSTNAM EMAIL) WHERE('CSTCTY = ''CHICAGO''') ORDERBY(CSTNAM) OUTPUT(*PDF) LOG(*SQL)

The SQL statement is written to the joblog, ready for analysis, cut/paste or anything else.

SELECT CSTNBR,CSTNAME,EMAIL FROM CUSTOMER WHERE CSTCTY = 'CHICAGO' ORDER BY CSTNAME

Let's take it to MS Excel. To do that, the only modification is to the OUTPUT parameter:

RUNQRYF CUSTOMER FLD(CSTNBR CSTNAM EMAIL) WHERE('CSTCTY = ''CHICAGO''') ORDERBY(CSTNAM)  OUTPUT(*CSV)

Now we have a CSV file  named CUSTOMER.CSV in our home directory and can do what we will.

Contact us today!

The Power of our Work with SQL Query File (WRKQRYF) Command

In addition to the RUNQRYF and RUNSQLF commands, the WRKQRYF (work with file fields using SQL Query File) command allows you to prompt fields and build your query. This is a very popular feature with end-users. It allows you to display a list of fields for any file (locally or on a remote system) and select the fields to be included in the queried report. Here is an image of the WRKQRY panel that appears.

Type in WRKQRYF QIWS/QCUSTCDT then press Enter.

Work with Query File Panel

The fields for the example database file QCUSTCDT are listed. Simply type in the sequence for the field to appear in the output, then press F10 to run the query. The RUNSQLF command is prompted and you can add any additional parameters at that time.

WRKQRYF_ChangeHeader

When you press Enter, the Change Column Headings dialog box appears. You can change the column headings and (assuming you have proper authority to do so) the column headings are changed and stored with the database file object.

 

After changing the column headings, future SQL Query File queries will use these new column headings, as will native DB2 SQL and other products.

Upon returning the the Work with File using SQL Query File screen, select the fields you want to include in the query, then press F10.

Note the LSTNAM column headings reflect the updated headings that you entered during WRKQRYF.

When you exit from the Query Results panel, you are returned to the WRKQRYF prompt, and your field selections are retained. If you ran the RUNSQLF or RUNQRYF commands directly, you are normally returned to Command Entry or the Menu from which they were run. But when you run them from within WRKQRYF, you are returned to it so you can further adjust your query. Press F3 when finished.

Check out SQL Query File for yourself. Download our free trial today.

Copyright © Cozzi Productions, Inc. All Rights Reserved.