Can you pass parms to Query/400 queries at run time?
Fred Gamache
Is it possible to pass a parameter to a Query/400 query to provide some
dynamic control of the selection criteria used in a query? I have some
pre-written queries that use date selections. I would like to give a
user the ability to change these dates at run time. These are long
running queries, so I need to run the query in batch after changing the
selection criteria.
Any suggestions?
Fred Gamache
fgam...@gic.gi.com
Dennis Jones
I know one method that has been mentioned is to create a parms file and
just use it in your query. You would still need to get the parms file
changed somehow. Perhaps you could create a small program to read the
parms, update the parms file and then submit the query.
Dennis
.
.
.
Vince Butler
I suffered a seizure and accidently replied when Fred Gamache
<fgam...@gic.gi.com> wrote:
>Is it possible to pass a parameter to a Query/400 query to provide some
>dynamic control of the selection criteria used in a query? I have some
>pre-written queries that use date selections. I would like to give a
>user the ability to change these dates at run time. These are long
>running queries, so I need to run the query in batch after changing the
>selection criteria.
>Any suggestions?
>Fred Gamache
>fgam...@gic.gi.com
Hi Fred,
Best way I know of is to put the date into a Physical file and add the
file in the join and match them by comparison in the file selections
"How to Join files". You can run any number of queries to this file
for selection purposes. The easiest way of updating the file would be
through an RPG program that will update to today's date.
Cheers,
Vince.
Webmaster/Profi Reifen
Fred Gamache <fgam...@gic.gi.com> schrieb im Beitrag
<32EE30...@gic.gi.com>...
> Is it possible to pass a parameter to a Query/400 query to provide some
> dynamic control of the selection criteria used in a query? I have some
> pre-written queries that use date selections. I would like to give a
> user the ability to change these dates at run time. These are long
> running queries, so I need to run the query in batch after changing the
> selection criteria.
>
> Any suggestions?
Yes, you can pass parameters into a query.
1) Make a cmd (or dspf-pgm) for getting the parameters you want (the user
can f.e. select the date he wants) and then submit all to batch.
2) In the query define at the "record selection screen" type:
field1 GE :var1
where field1 is the fieldname from the file and var1 is the name of the
variable field with the selected data. Don't forget the ":" !! You can use
GE, LE..., RANGE, LIST and so on.
When you press enter in this query screen you will get another screen.
There you must define "1" and a dummyfile in the FILE parm !! (I always
write "1 DUMMY/DUMMY" in this fields).
3) Don't use runqry. For executing this query type
STRQMQRY lib/query ALWQRYDFN(*YES) SETVAR((value1 value2 ...))
where value1, value2 etc. are the values from the selected data the user
typed in!
Regards
Helmut Salzer
--
Helmut Salzer
webmaster Profi Reifen Service Ges.m.b.H.
Office: pr...@profi-reifen.co.at
http://www.profi-reifen.co.at/profi/
Privat: salz...@ping.at
http://members.ping.at/salzer.h/as400.htm
Colin Grierson
Fred Gamache wrote:
>
> Is it possible to pass a parameter to a Query/400 query to provide some
> dynamic control of the selection criteria used in a query? I have some
> pre-written queries that use date selections. I would like to give a
> user the ability to change these dates at run time. These are long
> running queries, so I need to run the query in batch after changing the
> selection criteria.
>
> Any suggestions?
>
> fgam...@gic.gi.com
No you cannot pass parameters, curse, swear, spit etc, however with a
bit of judicious date arithmetic you can often achieve what you need.
For example if you want to extract only records for the previous month
your date being in CCYYMMDD form....
define a field LASTMONTH as (YEAR(CURRENT(DATE)-1 MONTH))*10000 +
(MONTH(CURRENT(DATE)- 1 MONTH))*100 + 1
This gives you the start of the previous month in the required form.
You can get the end date in a similar way. Read the query help text as
there is quite a lot you can do in this area and several ways to do it.
Note that CURRENT(DATE) is always the system date not the job date -
which is irksome at times.
Take care and have fun.
Cheers Colin
Helmut Salzer
> No you cannot pass parameters, curse, swear, spit etc, however with a
> bit of judicious date arithmetic you can often achieve what you need.
That's NOT true - see my reply above
Helmut Salzer
@aga.dk NKP
Webmaster/Profi Reifen <pr...@profi-reifen.co.at> skrev i artiklen
<01bc0dbc$0b8f88a0$0dbe9ac1@8627hvr61335>...
> > dynamic control of the selection criteria used in a query? I have some
> > pre-written queries that use date selections. I would like to give a
> > user the ability to change these dates at run time. These are long
> > running queries, so I need to run the query in batch after changing the
> > selection criteria.
> >
> > Any suggestions?
>
--
Nils Kilden-Pedersen
Douglas L. Blake
Fred Gamache <fgam...@gic.gi.com> wrote:
>Is it possible to pass a parameter to a Query/400 query to provide some
>dynamic control of the selection criteria used in a query? I have some
>pre-written queries that use date selections. I would like to give a
>user the ability to change these dates at run time. These are long
>running queries, so I need to run the query in batch after changing the
>selection criteria.
>
>Any suggestions?
>
>Fred Gamache
>fgam...@gic.gi.com
Another approach is to use a CPYF with selecting records matching
your date field and copy the file to QTEMP. Then execute your query
over the new file in QTEMP.
This works pretty well if your file is not very large, but it can be
quite a system hog otherwise.
Douglas Blake
Mike Albert Leasing, Inc.
Cincinnati, OH
Tim
Fred Gamache <fgam...@gic.gi.com> wrote:
>Is it possible to pass a parameter to a Query/400 query?
No, but you can get round the problem by writing your parameters to a
file and then joining this single record file to the database files in
your query.
Aternatively, (and also far more effectively), you could write a query
manager query, which accepts SQL statements or can prompt you for
instructions much like Query/400. You can access query manager to
create a query by running the command STRQM. Queries created in this
way can be run from a CL program using the command STRQRMQRY (Start
Query Manager Query) which allows you to pass parameter variables to
the query. If you need more specific info, contact me at
ba...@global.co.za
@aga.dk NKP
Helmut Salzer <pr...@profi-reifen.co.at> skrev i artiklen
<01bc0de1$3c7f2720$06be9ac1@8627hvr61335>...
Helmut, can you please post some sourcecode on this, cause I can't seem to
get it to work?
Thanks
--
Nils Kilden-Pedersen
Vince Butler
I suffered a seizure and accidently replied when "NKP" <trefork @
aga.dk> wrote:
>Webmaster/Profi Reifen <pr...@profi-reifen.co.at> skrev i artiklen
><01bc0dbc$0b8f88a0$0dbe9ac1@8627hvr61335>...
>> Fred Gamache <fgam...@gic.gi.com> schrieb im Beitrag
>> <32EE30...@gic.gi.com>...
>> > Is it possible to pass a parameter to a Query/400 query to provide some
>> > dynamic control of the selection criteria used in a query? I have some
>> > pre-written queries that use date selections. I would like to give a
>> > user the ability to change these dates at run time. These are long
>> > running queries, so I need to run the query in batch after changing the
>> > selection criteria.
>> >
>> > Any suggestions?
>>
[Clip]
>> 3) Don't use runqry. For executing this query type
>> STRQMQRY lib/query ALWQRYDFN(*YES) SETVAR((value1 value2 ...))
>>
^^^^^^^^^^
This is SQL/400 Query manager (officially called "IBM DB2 Query
Manager and SQL Development Kit for OS/400"), not Query/400. The
original query was for Query/400.
>> where value1, value2 etc. are the values from the selected data the user
>> typed in!
>>
>> Regards
>>
>> Helmut Salzer
>>
>> --
>> Helmut Salzer
>>
>> webmaster Profi Reifen Service Ges.m.b.H.
>> Office: pr...@profi-reifen.co.at
>> http://www.profi-reifen.co.at/profi/
>> Privat: salz...@ping.at
>> http://members.ping.at/salzer.h/as400.htm
>>
>>
>>
>Am I the only one, who can't get this to work?
Not surprised. He's talking a totally different product from
Query/400. Query/400 has NO method of directly receiving parameters.
Read the Manual! The only way is to put the parameter into a file and
match records.
>--
>Nils Kilden-Pedersen
Vince (Why do computers always need an upgrade?) Butler.
Gary Guthrie
> Not surprised. He's talking a totally different product from
> Query/400. Query/400 has NO method of directly receiving parameters.
> Read the Manual! The only way is to put the parameter into a file and
> match records.
Sorry, but this is not accurate. Yes, you can use a join and pass parameters, but...
You can create a dependent query. Dependent queries accept parameters. You cannot run
a dependent query directly with RUNQRY. You can use it in an Office document for
merging, etc.; it can be used in conjunction with another query for invocation, etc.
You can read up on dependent queries in the manuals.
You CAN run the query without these methods using the STRQMQRY command. STRQMQRY does
not require that the query object be a Query Management Query to run. You can specify
the ALWQRYDFN(*YES) parameter value and information about the query will be extracted
from the Query/400 *QRYDFN object. If you do not use the SETVAR keyword for any of the
variables used in the Query/400 object you will be prompted for the parameter value.
The SETVAR keyword allows you to bypass the prompt for parameter value and pass the
value(s) directly in.
An example of specifying a dependent query by using variable info for record selection
would be something like:
CompanyNo EQ :CompNum
Notice the : before CompNum - this means it is to be variable information which the
query will get from another source. When you are defining the Query/400 object it will
ask if the values are coming from another query or from a file. You can specify
anything you wish if you plan to use as Helmut has suggested, with the STRQMQRY command.
I always specify from a Query and give it the name DUMMY. The DUMMY query doesn't even
have to exist. You just need to enter it to continue on with the Query/400 definition.
Gary Guthrie
@aga.dk NKP
Gary Guthrie <ggut...@airmail.net> wrote in article
<32F125...@airmail.net>...
this:
ODORDN EQ :ORDRE
which I call with this CL program:
PGM PARM(&ORDRENR)
DCL VAR(&ORDRE) TYPE(*CHAR) LEN(6)
DCL VAR(&ORDRENR) TYPE(*CHAR) LEN(6)
STRQMQRY QMQRY(NKPTEST/QTEST) ALWQRYDFN(*YES) +
SETVAR((ORDRE &ORDRENR))
ENDPGM
When run, it gives me this joblog error:
5>> ? CALL ?*PGM(NKPTEST/CTESTQ) PARM('F00150')
Query derived from *QRYDFN object QTEST in NKPTEST, code 00.
Column F00150 not in specified tables.
RUN QUERY command failed with SQLCODE -206.
RUN QUERY command ended due to error.
STRQMQRY command failed.
Function check. QWM2701 unmonitored by CTESTQ at statement 0000000600,
instruction X'0000'.
QWM2701 received by procedure CTESTQ. (C D I R)
QWM2701 received by procedure CTESTQ. (C D I R)
To me, it looks like the value that I transfer as a parameter ('F00150'),
and is then inserted in the value field through variable &ORDRENR, is
conceived as a column name.(?)
--
Nils Kilden-Pedersen
tli...@educ.state.ak.us
> DCL VAR(&ORDRENR) TYPE(*CHAR) LEN(6)
>
> STRQMQRY QMQRY(NKPTEST/QTEST) ALWQRYDFN(*YES) +
> SETVAR((ORDRE &ORDRENR))
>
>
>and is then inserted in the value field through variable &ORDRENR, is
>conceived as a column name.(?)
Exactly right. Because the variable is *CHAR, it *must* be passed into the
STRQMQRY *with quotes included*. Otherwise, the query function will not
see F00150 as a value but rather as a column name.
Concatenate quotes before and after &ORDRENR and place the result in
a *CHAR 8 variable. This is what then gets passed to STRQMQRY.
Note that the query function would have worked fine if &ORDRENR had
been a numeric variable being tested against a numeric column. When this
same type of facility is used in a QM query and the resulting SQL is looked
at, it becomes a little clearer what's going on.
Gary Guthrie
Nils,
Your program is listed below:
PGM PARM(&ORDRENR)
DCL VAR(&ORDRE) TYPE(*CHAR) LEN(6)
DCL VAR(&ORDRENR) TYPE(*CHAR) LEN(6)
STRQMQRY QMQRY(NKPTEST/QTEST) ALWQRYDFN(*YES) +
SETVAR((ORDRE &ORDRENR))
ENDPGM
You need to concatenate quotes around the value because it is character.
Consider the code below which has been slightly modified from your code:
PGM PARM(&ORDRENR)
DCL VAR(&ORDRENR) TYPE(*CHAR) LEN(6)
DCL VAR(&ORD2)TYPE(*CHAR) LEN(8)
CHGVAR VAR(&ORD2) VALUE('''' *CAT &ORDRENR *CAT '''')
STRQMQRY QMQRY(NKPTEST/QTEST) ALWQRYDFN(*YES) +
SETVAR((ORDRE &ORD2))
ENDPGM
You do not need to declare ORDRE in the program.
If the parameter is numeric you do not concatenate the quotes around the
value.
Hope this helps.
Gary Guthrie
Marco Antunes
> > Gary Guthrie
> >
> Then why can't I get it to work? I have a query with record selection like
> this:
> ODORDN EQ :ORDRE
>
>
> Nils Kilden-Pedersen
If I'm correct you shoud change the :ORDRE to &ORDRE. The problem is if
you use :<> query master expects a column name and not a variable.
Hope this help.
__________________________________________________________
Marco Antunes
** Opinions expressed are my own and do not necessarily **
** reflect those of my employer! **
gary...@aol.com
In article <01bc0f44$973a3ba0$4bab...@nkp.kbh.aga>, "NKP" <trefork @
aga.dk> writes:
>When run, it gives me this joblog error:
> 5>> ? CALL ?*PGM(NKPTEST/CTESTQ) PARM('F00150')
> Query derived from *QRYDFN object QTEST in NKPTEST, code 00.
> Column F00150 not in specified tables.
> RUN QUERY command failed with SQLCODE -206.
> RUN QUERY command ended due to error.
> STRQMQRY command failed.
> Function check. QWM2701 unmonitored by CTESTQ at statement
0000000600,
> instruction X'0000'.
> QWM2701 received by procedure CTESTQ. (C D I R)
> QWM2701 received by procedure CTESTQ. (C D I R)
>
>To me, it looks like the value that I transfer as a parameter ('F00150'),
>and is then inserted in the value field through variable &ORDRENR, is
>conceived as a column name.(?)
You are right. add a CL line to change variable to have quotes before and
after it. Then it will work.
CHGVAR Var(&order) value(' " ' *cat &order *bcat ' " ')
This is required when the vaiables are alpha numeric.
Gary W. West (consultant/contract developer - Software Solutions, Inc.)
Samiam1525
What we use is a Query we've called "Datefile" that allows us to define
dates in the Result field. We can put in any number of results. We then
run qry and save the results to a disk file. You can then use the disk
file as on of your files for the actual query. I use WRKJOBSCDE to run
various queries automatically (like 1-5am). Hope this helps. Sam in
Panama City FL
cv...@aol.com
In article <01bc0dea$3ab54d80$4bab...@nkp.kbh.aga>, "NKP" <trefork @
aga.dk> writes:
>Am I the only one, who can't get this to work?
Works for me. V3R7.
Theo Frieling
>Aternatively, (and also far more effectively), you could write a query
>manager query, which accepts SQL statements or can prompt you for
>instructions much like Query/400. You can access query manager to
>create a query by running the command STRQM. Queries created in this
>way can be run from a CL program using the command STRQRMQRY (Start
>Query Manager Query) which allows you to pass parameter variables to
>the query. If you need more specific info, contact me at
I also use above method but I wonder if I also can pass a parameter
which will be shown on the header or footer. The infor I want to put
there is the same info I pass as a parameter. Do you now if this is
possible and if so, how to accomplisch this.
Best regards, Theo.
Helmut Salzer
This is much more easier as query-parms:
1) In the STRQM screen convert your definition with option 10 from "PROMPT"
to "SQL" type.
2) Update the new sql and change the constant values to var-names
Example: old ---> .. where field1 = 'ABC'
new ---> ... where field1 = &var1
3) Start the sql with STRQMQRY and give your parms in the SETVAR parm.
Greetings
Helmut Salzer
webmaster Profi Reifen Service Ges.m.b.H.
Office: pr...@profi-reifen.co.at
http://www.profi-reifen.co.at/profi/
Privat: salz...@ping.at
http://members.ping.at/salzer.h/as400.htm
Theo Frieling <thf...@pi.net> schrieb im Beitrag
<32fb1b74...@news.pi.net>...
Orangtang
As far as I know, you can't. What we have done in our shop is re-write
the AS/400 queries in Query Manager (STRQM). With Query Manager, you can
pass variables used for record selection...Ron
Orangtang
To clarify this response...this is not a answer to passing parms to an
AS/400 query. This is an answer to passing parms to a Query Manager query
which is a totally separate query function on the AS/400...Ron
Orangtang
You cannot pass parms to AS/400 query..You must use Query manager to do
this....Ron
Orangtang
Easiest way is to re-write the query in Query manager..You can then set up
variables in your record selection and pass parms when submitting the
query..Ron
Alex Bunardzic
thf...@pi.net (Theo Frieling) wrote:
>>Aternatively, (and also far more effectively), you could write a query
>>manager query, which accepts SQL statements or can prompt you for
>>instructions much like Query/400. You can access query manager to
>>create a query by running the command STRQM. Queries created in this
>>way can be run from a CL program using the command STRQRMQRY (Start
>>Query Manager Query) which allows you to pass parameter variables to
>>the query. If you need more specific info, contact me at
>I also use above method but I wonder if I also can pass a parameter
>which will be shown on the header or footer. The infor I want to put
>there is the same info I pass as a parameter. Do you now if this is
>possible and if so, how to accomplisch this.
>Best regards, Theo.
Funny thing, I couldn't make that happen. I did try to pass the
parameters in the STRQRYMQRY command, but it complained that it
somehow expected these parameters to be coming from a HLL program, not
the command line.
What gives?
Thanks.
Alex
Rob Freeling
In article <19970209135...@ladder01.news.aol.com>, Orangtang
<URL:mailto:oran...@aol.com> wrote:
>
> You cannot pass parms to AS/400 query..You must use Query manager to do
> this....Ron
Yes, you can!
The idea is the following. Make a small (RPG) program that fills a file, for
instance with two fields, a start date and an end date. Make a query that
joins this file with your database. Make a CL prog with a command interface,
that takes the input parms , calls the RPG and then run the query.
Grtz,
--
Rob Freeling <mailto:rfre...@inter.nl.net>
Consultant AS/400 <mailto:RFre...@inetgate.capvolmac.nl>
Cap Volmac Systems Management <mailto:RFre...@ntprsmail.capvolmac.nl>
... Clones are people two.