REPORTS

www.cozTools.com

www.MidrangeNews.com

Contact Us

iQuery

SQL iQuery™ iQuery

More on iQuery Script Tool

SQL iQuery's iQuery allows you to save and run SQL statements within source file members or IFS Text files. This provides a Reporting option that can be made available to end-users. Once created, you can place the RUNIQRY command behind a Menu option, bury it in a CL program, or even call it via the web. This allows developers (or advanced users) to go in and change the query without the need to modify anything other than the SQL statement. You could call it: Externally Described SQL.

NOTE: With SQL iQuery's "write one and done" design, you can output to any available format and get similar results. For example purposes, we are using OUTPUT(*) in the examples below but output to the web, Excel, PDF and other formats are all supported in an identical fashion.

SQL Source Members

You may create an SQL statement using any method you prefer, such as STRSQL or one of the myriad web and Windows-based applications and then save that SQL statement into a source member. We use QSQLSRC as our default SQL source file name, but any source file name may be used. In addition, text files on the IFS may be used, and are very popular with our users who also service Linux installations. For illustration purposes, we will use QSQLSRC.

Make sure you have an SQL source file created in a library. We use library PRODLIB throughout our examples so replace it with your own library name. To create the source file, use the IBM-supplied CRTSRCPF command.

CRTSRCPF PRODLIB/QSQLSRC RCDLEN(112)

Any value for the RCDLEN parameter is supported, however in practice we find that 112 or larger is best for SQL. Remember, if you need to use SEU/EDTSRC, the maximum source record length is 240 bytes. For our own internal SQL source files we use a standard length of RDCLEN(112).

Below is a (very) basic SQL statement saved into the source member named CUSTLIST.

 FMT **  ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+...

        *************** Beginning of data **************

0001.00 SELECT * FROM QIWS/QCUSTCDT

        ****************** End of data *****************

To run the SQL statement from within this source member, use the RUNIQRY command as follows:

RUNIQRY SRCMBR(CUSTLIST) SRCFILE(PRODLIB/QSQLSRC)

RUNSQLF Resultset

Not bad results for so little effort. You can place the RUNIQRY command within a CL program or behind a menu option and your end-users can run it anytime with knowing anything about SQL.

Report Codes

To provide customization of the SQL report or result set, SQL iQuery supports a growing number of Report Codes that may be embedded within the source member or IFS text file. Some of these codes may be used in place of RUNIQRY parameters (such as the report headings codes) while others offer additional unique controls. The following tables lists the available codes.

Code Type Description
Hn Text Main heading/titles for the result. H1, H2, and H3 are currently supported and correspond directly to the Heading Line.
H2:Customer List
URL datalink Specify a URL (datalink) to be used with the associated "TAG" column. The TAG code is used to identify the column that is enclosed in the HTML anchor tag with the href assigned this URL. The datalink may be a fixed URL string, or another column (field) name that contains the URL to be used. For example, if the field named EMAIL contains the email address for the field named CONTACT, you might specify the following:
URL: %EMAIL%

TAG: CONTACT

The URL value may be a full URL, such as www.google.com or the name of a database field enclosed in percent signs, such as %EMAIL% or a combination of both, such as www.google.com/%email%

The value of the field (if specified) is embedded into the URL or it may be the entire URL itself.

TAG field name Specify the name of a column/field that is enclosed in the <a href=> tag specified on the associated URL code. When HTML output is selected, this field is assigned a hyperlink based on the value specified for the associated URL code.
HTTP_BUFFER integer Specify the size (in kilobytes) for the HTTP buffer when sending the results to the web browser. The default is 64 (64 kilobytes) but may be changed using this code. Please note that the IBM i HTTP web server has an issue with buffers that exceed 80k.
HTTP_BUFFER:32
HTTP_CSS datalink Specify the cascading style sheet name and location when output to the web. The default is "/style/webiqry.css"
HTTP_CSS:/home/website/style/custom.css
CMD or CL IBM i CL command Specify a CL command to be run before the SQL statement is processed. This is often used for ADDLIBLE, CHGCURLIB and OVRDBF commands. However any valid CL command or "CALL mypgm" may be specified. The command is run when the source file is loaded, before the SQL statement is processed. If SQL Server Mode is active for the job loading the source member, the command is routed to and run in the server job. Any CL command be run. The command is processed using QCMDEXC which is wrapped in the IBM-supplied UDF of the same name in QSYS2 library. You may specify more than one command by included multiple CMD/CL codes. Note the maximum length of a command is the same as the source member SRCDTA length. That is a single command may not span multiple lines.
cmd:addlible prodlib

cmd:call mypgm

The Heading codes H1, H2, H3 will be the codes you use most frequently. However, adding a library using ADDLIBLE or CHGCURLIB is also somewhat common. For those commands, the CMD code is normally used.

The Header Code syntax is:

Hn:this is my header text

The letter 'H' followed by the heading line number n (1, 2 or 3) followed by a colon. After the colon, specify up to 50 characters of heading text description. Leading spaces are ignored. To illustrate, let's enhance heading line 2 of the basic listing to more accurately identify what it the listing contains. In addition, I will add a comment line and sort the resultset.

 FMT **  ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6

        *************** Beginning of data **************

0000.01   -- Demo Customer File Query

0001.00 H2:Customer Master Listing

0002.00 SELECT CUSNUM,CITY,STATE,BALDUE

0003.00  FROM QIWS/QCUSTCDT

0004.00  ORDER BY baldue DESC

        ****************** End of data *****************

In this example, I've changed the H2 line of the headers to "Customer Master Listing". This replaces line 2 of the default headings with the user-specified content. In addition, I've specify only the fields the end-user wants to view, and sorted the report results in descending order by balance due. The modified query produces a report that looks like the following when run.

Now we have a report with just the information the end-user wants. We can also change the primary heading using H1 or add a third line of headings using H3. But typically H2 is customized most often.

Note that I've added a comment to the source code. Standard SQL comments are identified by two consecutive dashes. SQL iQuery supports comments on individual lines only (not embedded on the same line with other codes or the SQL statement). Both SQL and RPG-style free-format // comments are supported. In addition if the first character of the line is the "hashtag" (or more officially a pound sign) symbol # the line is ignored. To be clear, when the # symbol is located in position 1 of the source line it causes the line to be ignored or "commented out" however if the # is the first character on the line and that character is NOT in position 1, the line is processed normally.

In the next example, I've included two CMD codes to run two ADDLIBLE commands before the SQL statement is run. These two ADDLIBLE commands add libraries PRODLIB and DATALIB to the library list while before performing the query. If they already exist, the errors are ignored.

 FMT **  ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6

        *************** Beginning of data **************

0000.01   -- Demo Customer File Query

0001.00 H2:Customer Master Listing

0002.00    -- Add PRODLIB and DATALIB to the library list

0002.01 cmd:addlible prodlib

0002.02 cmd:addlible datalib

0003.00 SELECT CUSNUM,CITY,STATE,BALDUE,S.cursales

0004.00  FROM QCUSTCDT

0005.00    -- Join to the Sales Information File in the DATALIB library

0005.01  LEFT OUTER JOIN SALESINFO S on cusnum=s.cusnum

0006.00  ORDER BY baldue DESC

        ****************** End of data *****************

Note that I've added comments to the source code to document what the various sections of the statement are doing.

 

Copyright © 2015 Cozzi Research All Rights Reserved.