In the environment I work there are many programs that produce "download reports". There are not paper reports, but extracts of file(s) that are sent, via email, to the requestor. Most of these use a Query to gather information together.
Many are coded like this:
RUNQRY QRY(QUERY1) |
Which means that when the Query is run it will use the files that are hard coded in the Query’s definition. Therefore, if I want to perform a test and use files in a testing library I have to change the Query.
Or if I want to change which file or library the output file is in I have to change the Query.
Fortunately, there is an easy way to overcome these issues by using the parameters in the RUNQRY command.
In the example below I have:
- "Soft coded" where the files are using the QRYFILE parameter.
- Set the output to be a file using the OUTTTPE parameter.
- Named the output file (QTEMP/OUTFILE), set the member to be output to (*FIRST = first member in the file), and what to do if the file already exists (*RPLFILE = replace existing file) in the OUTFILE parameter.
- As I do not want to print the definition of the query I use the PRTDFN parameter.
- And I want anyone to use the output file so I give it *ALL authority in the AUT parameter.
RUNQRY QRY(QUERY1) +
QRYFILE((*LIBL/FILE1) +
(*LIBL/FILE2)) +
OUTTYPE(*OUTFILE) +
PRTDFN(*NO) +
OUTFILE(QTEMP/OUTFILE *FIRST *RPLFILE) +
AUT(*ALL)
|
I can take the same query and change it to print out by changing the OUTTYPE parameter. I also:
- Want a detail report using the OUTFORM parameter.
- Want the page to be 198 characters wide, which I do using the FORMSIZE parameter.
RUNQRY QRY(QUERY1) +
QRYFILE((*LIBL/FILE1) +
(*LIBL/FILE2)) +
OUTTYPE(*PRINTER) +
OUTFORM(*DETAIL) +
PRTDFN(*NO) +
FORMSIZE(*RUNOPT 198)
|
The only issue I have with this is that it generates the Query Cover Page. The flag to not print the Cover Page is within the Query, but it is not able to prevent the cover page from being generated when using the RUNQRY command.
If you want to change the name of the spool file from QPQUPRFIL to something more meaningful I describe how to do it in the post Changing name of Query/400 print file.
You can also pass parameters to a Query, read Passing parms to a Query to learn how to do it.
In my opinion by using these parameters also makes it easier for the programmer, at a glance, to get an idea what the Query is doing.
You can learn more about the RUNQRY command on the IBM website here.
This article was written for IBM i 7.1, and it should work with earlier releases too.
Something to look at would be to create a utility to build the SQL from the Query
ReplyDeleteIt is possible to get the SQL statement out of Query, see the post Getting the SQL statement of Query
DeleteBuenos dias, muy interesante respecto a los ejecutables del RunQuery, yo normalmente ejecuto los Querys por medio de paramatros donde el usuario asigna las diferentes compañias a proceder y los datos de cada uno, en las mismas bibliotecas o diferentes bibliotecas ej:
ReplyDelete*
OVRDBF FILE(PLA931XX) TOFILE(QS36F/PLA931XX)
*
RUNQRY QRY(PLANIOBJ/PLA931XX) QRYFILE((QS36F/PLA931XX)) +
OUTTYPE(*PRINTER) OUTFORM(*DETAIL) PRTDEV(*PRINT)
*
Translation using Google Translate reads
DeleteGood morning, very interesting about executables RunQuery, I typically run through the Querys parameters where you assign the different companies to come and each data in the same library or different libraries eg ...
Kevin, look into RTVQMQRY ALWQRYDFN(*YES)
ReplyDelete