How do I pass a parameter to a query?
Tom Hightower
Is there some way that I can pass a substituted parameter to a pre-defined
query from within a CL program? If so, could you please give an example?
TIA...
G. Wayne Calvert, Information Systems Manager
"Tom Hightower" <si...@arn.net> wrote:
>Is there some way that I can pass a substituted parameter to a pre-defined
>query from within a CL program? If so, could you please give an example?
STRQMQRY QMQRY(PO_VS_AP03) OUTPUT(*OUTFILE) +
OUTFILE(QTEMP/GLCMTRP1) ALWQRYDFN(*YES) +
SETVAR((YY &YEAR) (PP &PERIOD))
In this example YY and PP are identified in the Query select record as
CMYR EQ :YY
AND CMJEFP EQ :PP
AND CMACCT RANGE '13000' '15999'
G. Wayne Calvert
jac...@twave.net
Information Systems Manager
JacksonLea
A unit of Jason Incorporated
dgra...@imperium.net
"Tom Hightower" <si...@arn.net> wrote:
>Is there some way that I can pass a substituted parameter to a pre-defined
>query from within a CL program? If so, could you please give an example?
>TIA...
The best you can do with Query/400 is to display the record selection
screen and allow the user to interactively make changes.
-Dave Grafton
Peter C. Styer
You can pass a parameter to a query using the STRQMQRY command. I received
the code from a consultant. She indicated there was an article in Midrange
Computing within the last year.
Define the variable in you selection criteria with a colon in front of the
field name.
Call the query in a CL program using:
STRQMQRY QMQRY(LIB/QUERY) QMFORM(*QMQRY) ALWQRYDFN(*YES)
SETVAR((QRYVAR &PGMVAR))
where: QRYVAR is defined in the query selection as :QRYVAR
&PGMVAR is defined in the CL or display file.
I hope this helps.
P. Styer
done...@echo-on.net
In article <01bbd821$3aedbd40$4490fecc@sitdh>,
"Tom Hightower" <si...@arn.net> wrote:
>
> Is there some way that I can pass a substituted parameter to a pre-defined
> query from within a CL program? If so, could you please give an example?
>
> TIA...
Tom:
Passing parameters is something that Query/400 is unable to do (I am
assuming your request is related to Query/400). However, depending on
what you are trying to do, you can sometimes do some variable data
selection. For
example:
1) Use CPYF before the query to do some selection. I have successfully
done some selection this way. For example, I use CL to run the query,
and
have passed a parameter into the CL program indicating that the first two
characters of FIELDA are '&PFX' and selecting out only records that match
the parameter passed. However, the parameter is used in the CPYF instead
of the RUNQRY. Then, of course, you query FILEB instead of FILEA and it
has only records that have the correct prefix in FIELDA.
CPYF FROMFILE(FILEA) TOFILE(FILEB) INCCHAR(FIELDA 1 *EQ &PFX)
OVRDBF FILE(FILEA) TOFILE(FILEB)
RUNQRY QRY(*LIBL/MYQRY)
2) The other way I have successfully selected records is to have the
select
criterion in another file, and then use Query/400 to join the files using
that information. The type of join would depend on what you are trying
to
achieve. Do you want records to be excluded if they match or included if
they match. Then query the new joined file instead of the original file.
These methods do require another step, but they have worked for me.
I am told that Query Manager has the ability to use variables in
selection. When I tried it, I didn't get it to work, but unfortunately I
haven't had much time to get back to it and try it again. You might ask
if anyone has used Query Manager this
way.
Debbie Gallagher
done...@echo-on.net
-------------------==== Posted via Deja News ====-----------------------
http://www.dejanews.com/ Search, Read, Post to Usenet
Igor Birman
Tom Hightower wrote:
>
> Is there some way that I can pass a substituted parameter to a pre-defined
> query from within a CL program? If so, could you please give an example?
>
> TIA...
There is one other way. The Query Manager query is probably the best
way, but you could always put your parameters in another file and have
your query reference this file. This other file would have only one
record in it. This technique will work with any query system on any
platform.
euc...@aol.com
we use OPNQRYF folowed by simple RPG for listing
h.j. schiwek
Ted Holt
>> Is there some way that I can pass a substituted parameter to a pre-defined
>> query from within a CL program? If so, could you please give an example?
You can join a 1-record file with the parameters in it using a
Cartesian product. See Flexible Date Selection with OPNQRYF in
Midrange Computing, March 96. Rich Grega originally used this
technique with Query/400.
=======================================================
Ted Holt
ted...@tsixroads.com
ho...@midrangecomputing.com
--------------------------------------------------------
Why fry your brain doing drugs? TV is legal and cheaper!
========================================================
John Edwards
The Ever-So-Wise ted...@email.tsixroads.com (Ted Holt) wrote:
->>> Is there some way that I can pass a substituted parameter to a pre-defined
->>> query from within a CL program? If so, could you please give an example?
->
->You can join a 1-record file with the parameters in it using a
->Cartesian product. See Flexible Date Selection with OPNQRYF in
->Midrange Computing, March 96. Rich Grega originally used this
->technique with Query/400.
You can do it without using OPNQRYF if it's simple. Below
is an example of one I did for a query we were running on a
nightly basis.
CL Example:
PGM
DCL VAR(&DATE) TYPE (*CHAR) LEN(6)
DCL VAR(&VDATE) TYPE (*CHAR) LEN(6)
/* Get date from operator for Query */
PMTOPR RTNVAR(&DATE) LEN(6) PROMPT('Please Enter +
Target Date (MMDDYY)') TYPE(*DATE)
/* Convert Input to DATE format that Table uses */
CVTDAT DATE(&DATE) TOVAR (&VDATE) FROMFMT (*SYSVAL) +
TOFMT(*YYMD) TOSEP (*NONE)
/* Run Query Manager Query */
STRQMQRY QMQRY(LIB1/TABLE001) OUTPUT(*) +
SETVAR((VDATE &VDATE))
ENDPGM
Query Manager Querey looks like this:
SELECT COUNT (*) FROM LIB1/TABLE001
WHERE WDATE = &VDATE
(1) 'WDATE' is the column name on this table.
====
If you get an error stating that it can't find a column
named &vdate (or something similar, my system is down for
backups, so I can't check it), you may have to concatonate single
quotes to either side of your variable.
If this happens, let me know and I'll forward the workaround
for it. This happened after we changed a lot of our table
formats, so it may happen with yours, or not.
John
Fritz get up for god's sake. Get up! They've killed Fritz. They've
killed Fritz. Those lousy stinking yellow fairies, they've killed
Fritz! Those horrible atrocity-filled vermin. Those despicable
animal warmongers. They've Killed Fritz Max - Wizards
oran...@aol.com
As far as I know, you can't pass a parameter using as/400 query..but you
can using as/400 Query Manager. Try using Query Manager instead. It has
all the functionality of as/400 query..and more..Ron
Steve Dillen
> As far as I know, you can't pass a parameter using as/400 query..but you
> can using as/400 Query Manager. Try using Query Manager instead. It has
> all the functionality of as/400 query..and more..Ron
>
through query manager -- joins based on unmatched records. To my knowledge
QMQRY has no facility for this type of join. The benefit of passing parms,
however, generally outweighs this limitation.