Solved

Passing Parm to AS400 query

Posted on 2003-12-02
6
3,945 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
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Suggested Solutions

Facebook has became the #1 social media platform. People share many funny videos there, yet you don't know how to download them? Now you can download Videos from Facebook in just 3 simple steps.
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…

713 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