Solved

Passing Parm to AS400 query

Posted on 2003-12-02
6
3,935 Views
Last Modified: 2007-12-19
Hi
I have a AS400 query I wanted to pass the parameters from CL program Can pass the parm to query ?
Thx
---Srini
0
Comment
Question by:sireeshamanchala
6 Comments
 
LVL 16

Accepted Solution

by:
theo kouwenhoven earned 50 total points
ID: 9864919
Hi sireeshamanchala,

You can give some parameters to a RUNQRY, but only the one you see on the RUNQRY command, but I think you like to give selection parameters.
No, that is not possible, but you can solve that as follow.

----------- Sample CL --------------
OPNQRYF    FILE((mylib/myfile)) QRYSLT('myfield *eq' |> &MYPARM')
CPYFRMQRYF FROMOPNID(myfile) TOFILE(mytemp)
RUNQRY QRY(MYLIB/MYQRY)
----------- Sample CL ------------

I'm sure there is an other way like using SQL (enentually imbedded in RPG)
but this is the easyest to manage.

Regards,

Murphey

0
 
LVL 14

Expert Comment

by:daveslater
ID: 9865250
Hi
The way Murphy has given is the easiest but if it is not what you want the only other way is to use Query Management Queries.
You can write the query then split it down into extraction and prints and pass it parms
use
GO CMDQM
to have a look


Dave
0
 

Author Comment

by:sireeshamanchala
ID: 9870822
Hi Murphey,
Weher do I define 'myfield'
Thx
---Srini
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 9872830
Hi sireeshamanchala,

myfield is one of the fields from the file you like to QRY
if this is an item file it can look like this:

OPNQRYF    FILE((mylib/ITEMFIEL)) QRYSLT('ITNBR *gt' |> &MYPARM1 *AND ITEMNBR *lt' |> &MYPARM2 *AND ITACTIV *EQ "Y" ')

will select all active items between parm1 and parm2.

:-)

have fun
0
 

Expert Comment

by:juvencio
ID: 10045113
1. Create a file that will "hold" your parameters values
2. Create the query reading the parameter file and selecting from the "real" data files.
3. You can aso prompt the user to fill in the parameter file before running the query.

Good Luck
0
 
LVL 27

Expert Comment

by:tliotta
ID: 10819513
Ummm... you can pass parms into queries -- either Query/400 *QRYDFNs or Query Manager *QMQRYs -- by properly defining the queries in the beginning and then running them correctly. Overall, QM queries are superior and also easier to pass parms into.

To pass parms into a *QRYDFN, create what's called a "dependent query". Use any dummy name when prompted for the dependent file for the dependent column value since you won't actually use an additional file anyway. To run, use the STRQMQRY command specifying ALWQRYDFN(*YES) and supplying the *QRYDFN name for the QMQRY() parm. Whatever you typed for the dependent column name will be prompted back to you for a value. You can instead supply this via the SETVAR() parm to avoid prompting, thereby "passing in a parm to Query/400".

To pass parms into QM queries, use standard substitution variables in the QM query SQL statement. The substitution variable names will be prompted for just as above when you run the QM query with STRQMQRY. Again, SETVAR() can be used to avoid prompting.

Tom
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many businesses neglect disaster recovery and treat it as an after-thought. I can tell you first hand that data will be lost, hard drives die, servers will be hacked, and careless (or malicious) employees can ruin your data.
A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question