RE: OPNQRYF with variable key names

We have a generic report submitter that does all of that, and it's been in
use, pretty much unmodified, since 1994. It works the same way as everyone
here describes the solution. There is one improvement I need to make: If
there is an exclusion, instead of coding '*AND FLD1 *NE "A" *AND FLD2 *NE
"B"...', you can say, '*AND *NOT %VALUES("A" "B")' (I tried that, it works
on V5R1).

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Jeffrey Young
Sent: Wednesday, June 22, 2005 11:13 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: OPNQRYF with variable key names

Marla,
In order to use variable field values in the KEYFLD keyword, you need to
construct your ENTIRE OPNQRYF command as a variable, and then use QCMDEXC to
execute it.
The QCMDEXC program will read your OPNQRYF command string and do what you
want.
Then all you need in to issue the OVRDBF SHARE(*YES) command and you are
good to go.


Marla Snow <MSnow@xxxxxxxxxxxxxxxxxx> wrote:
Can anyone help? I have an RPG pgm that allows the user to select up to 3
sort fields for their data of 8 possible choices. I had thought the best way
(and easiest to maintain) would be to use parameters for the KEYFLD that
would just populate the names of the fields that define the sort. When the
program runs, the OPNQRYF returns no records. Here is an example of what the
statements look like in the CL pgm.

OVRDBF FILE(SA0025WK) SHARE(*YES) 

IF COND((&OPT1 *NE '1') *AND (&OPT2 *NE '1') + *AND (&OPT3 *NE '1'))
THEN(OPNQRYF +
FILE((SA0025WK)) KEYFLD((&SFLD1) (&SFLD2) +
(&SFLD3))) 

IF COND(&OPT1 *EQ '1') THEN(OPNQRYF +
FILE((SA0025WK)) KEYFLD((&SFLD1 *DESCEND) +
(&SFLD2) (&SFLD3))) 

When that didn't work, I created an RPG program (after seeing some
suggestions to others problems) that would create the entire OPNQRYF and
execute that statement. Once again, I didn't get any records when the
following RPG program tried to read the file. When I debug the RPG pgm, the
OPNQRYF statement looks perfect but still has problems. Is there something I
am missing? Has anyone done something like this before?

Here is the pgm that creates the OPNQRYF in RPG:

D Length S 15P 5
D Command s 100
*
d cmd1 S 33
*
***************************************************************
c eval cmd1 = 'OPNQRYF FILE((SA0025WK)) KEYFLD(('
c eval command = %trim(cmd1) + fld1
*
c if opt1 = '1' 
c eval command = %trim(command) + ' *DESCEND' 
c endif
*
c if opt2 = ' ' 
c eval command = %trim(command) + '))' 
c else
c eval command = %trim(command) + ') (' + fld2 c if opt2 = '1' 
c eval command = %trim(command) + ' *DESCEND' 
c endif
c endif
*
c if opt3 = ' ' 
c if opt2 <> ' ' 
c eval command = %trim(command) + '))' 
c endif
c else
c eval command = %trim(command) + ') (' + fld3 c if opt3 = '1' 
c eval command = %trim(command) + ' *DESCEND' 
c endif
c eval command = %trim(command) + '))' 
c endif
*
C Eval Length = %Size(Command)
C Call 'QCMDEXC' 55
C Parm Command
C Parm Length
*
c move *on *inlr
c return
***************************************************************
c *InzSr begsr
* ***********
c *entry plist
c parm opt1 1
c parm opt2 1
c parm opt3 1
c parm fld1 6
c parm fld2 6
c parm fld3 6
*
*
c endsr 



Thanks for any advise that anyone has!


Marla Snow
msnow@xxxxxxxxxxxxxxxxxx

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.



Jeff Young
Sr. Programmer Analyst
Dynax Solutions, Inc.
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions
V5R2 IBM  Certified Specialist- e(logo) server i5Series Technical Solutions
Designer V5R3

IBM  Certified Specialist- e(logo)server i5Series Technical Solutions
Implementer V5R3

  
 







__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.