Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Passing Parm to AS400 query

Posted on 2003-12-02
6
Medium Priority
?
4,088 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 200 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Welcome to 2018! Exciting things lie ahead in the world of tech. To start things off, we compiled great member articles on how to stay safe, ways to learn, and much more! Read on to start your new year right.
As a matter of fact, Outlook OST files are of much importance in relation to Exchange mailbox. OST files are independent as they are simply copy of data of a user’s mailbox on Exchange Server. Though, if the server’s status is changed or it is dama…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Suggested Courses

580 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