To create a query that allows parameters

Almost every shop has users running several queries that all they do is change the select records option and then run the query. You can create a better option that will allow a query to accept parameters. Do the following:

1. Create/change query as usual via WRKQRY. Enter your select records values as usual.

2. Create a query management query by retrieving the source from the query created at previous step. Notice that a file QQMQRYSRC was created to store this source. This step will create a Query source.

RTVQMQRY QMQRY(BPCSUSR62/OLDQRY) SRCFILE(BPCSUSR62/QQMQRYSRC) ALWQRYD(*YES)

3. Using SEU, modify source created at step 2. Substitute desired values with variables starting with &. For example:

WHERE TPROD = IPROD
AND( IFRFG = 'Y'
AND TTYPE IN ('U', 'A', 'B', 'RM')
AND TTDTE BETWEEN &FDATE AND &TDATE)
ORDER BY T01.TPROD ASC, T01.TTYPE ASC, T01.TRES ASC

4. Using this modified source, create a new query management query. To create it use following:

CRTQMQRY QMQRY(BPCSUSR62/NEWQRY) SRCFILE(BPCSUSR62/QQMQRYSRC) SRCMBR(DRPITH)

5. Now, instead of using RUNQRY command use STRQMQRY to run new query. For example:

STRQMQRY QMQRY(DRPITH) OUTPUT(*OUTFILE) +
OUTFILE(QTEMP/DRPITH) ALWQRYDFN(*NO) +
SETVAR((FDATE &FDATE) (TDATE &TDATE))

You can then ask for parameters using a screen within a CLP and then pass this parameters to the STRQMQRY command. You can create a temp file based on your results to be used to print a report or used for further processing.


RTVQMQRY QMQRY(BPCSUSR62/OLDQRY) SRCFILE(BPCSUSR62/QQMQRYSRC) ALWQRYD(*YES)

CRTQMQRY QMQRY(BPCSUSR62/NEWQRY) SRCFILE(BPCSUSR62/QQMQRYSRC) SRCMBR(DRPITH)

STRQMQRY QMQRY(DRPITH) OUTPUT(*OUTFILE) OUTFILE(QTEMP/DRPITH) ALWQRYDFN(*NO)
SETVAR((FDATE &FDATE) (TDATE &TDATE))

This was first published in March 2001

Dig deeper on RPG iSeries programming

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close