OPNQRYF embedded RPG/400 example

[Guest.Visitor]

Hi, does anybody have an example of multiple selections with OPNQRYF (can be executed more than once doing a new selection) in RPG/400 (builds the QRYSLT with MOVEs and CATS) than can send ? I had searched in the IBM manuals but didnt found any example. Thanks, Silvio.

[Guest.Visitor]

Hi, You got to have one CL program with OPNQRYF command and should recieve a parameter from RPG program ;a string which contains your select statement to be used in QRYSLT parameter. Build your query statement and send it as a parameter to the CL program and it will execute the opnqryf based on the query statement.This way you can build different query statements in RPG and send it as a parameter to the CL program to execute different queries depending on the selection criteria. If you need example, email your specifications and I will send the code for you . good luck! Anil

[Guest.Visitor]

Hi Silvino, I regularly perform OPNQRYFs in RPG400. All overrides etc are performed through QCMDEXC and the command strings are built with the help of Compile Time arrays. I should probably update this to use constant fields with the new D Specs but maybe I'm just a bit lazy. A couple of things to watch out for. When inserting numeric selections into a string you will need to move these to an alphanumeric workfield first. I always use a double quote for character field selections (") as this will be a quote within a quote (of the overall QRYSLT selection). eg. QRYSLT('REP *EQ "SMITH"'). I always have a compile time array called SG where I define a single quote ** ARRAY SG ' with a D-Spec entry; D SG S 1 DIM(1) CTDATA PERRCD(1) This is used to enclose the QRYSLT expression. It's always handy to have a single quote defined in a variable. Typically I will also have four other arrays/strings to execute the normal flow of OPNQRYF (Override with Share, OPNQRYF, CLOF and DLTOVR respectively) An example is shown below; ** ARRAY OVR OVRDBF FILE(HD003AL1) SHARE(*YES) SECURE(*YES) ** ARRAY OPN OPNQRYF FILE((HD003AL1)) QRYSLT( ) KEYFLD((CHID *DESCEND)) SEQONLY(*YES 14) OPTIMIZE(*FIRSTIO) ** ARRAY CLS CLOF OPNID(HD003AL1) ** ARRAY DLT and the D-SPEC entries; D OVR S 80 DIM(1) CTDATA PERRCD(1) D OPN S 80 DIM(3) CTDATA PERRCD(1) D CLS S 80 DIM(1) CTDATA PERRCD(1) D DLT S 80 DIM(1) CTDATA PERRCD(1) The OPNQRYF string is normally the most complex as you may also want to Soft Code KEYFLD and MAPFLDs. Using the above commands with a User Defined Open (USROPN)will facilitate multiple opens. The above sample code is part of an F17=Subset feature of a "Work with" Application. The selection screen is shown below; Type input. Press Enter. Status . . . . . . . . . : Compare . . . . . . . . *EQ *EQ, *NE. Setting . . . . . . . . *ALL *ALL, 0=Open, 1=WIP, 2=Closed. Analysis Groups . . . . : Requested By . . . . . . *ALL *ALL, Code, F4 for List. Received By . . . . . . *ALL *ALL, Code, F4 for List. Assigned To . . . . . . *ALL *ALL, Code, F4 for List. Source . . . . . . . . . *ALL *ALL, Code, F4 for List. Location . . . . . . . . *ALL *ALL, Code, F4 for List. Classification . . . . . *ALL *ALL, Code, F4 for List. Priority . . . . . . . . *ALL *ALL, Code, F4 for List. Period in view . . . . . : Based on Date . . . . . 1 0=Input, 1=Updated, 2=Closed. From . . . . . . . . . . 070300 *ALL, Date DDMMYY. To . . . . . . . . . . . *ALL *ALL, Date DDMMYY. As you can see the selections are quite comprenhensive. The RPG400 code for the open is shown below. The QRYSLT expression is edited in a further subroutine. I would always recommend editing complex portions of the OPNQRYF seperately (eg. MAPFLDs KEYFLDs). It has a seperate D-SPEC entry D @QRYSLT S 500 This example is 500 long the limitation on this escapes me for the moment but it's a lot bigger. C*------------------------------------------------------------* C* $OPEN Open the File C*------------------------------------------------------------* C $OPEN BEGSR C* ~~~~~ ~~~~~ C* Override to file C* C MOVEL(P) OVR(1) CMDSTR C EXSR $EXCMD C* C EXSR $QRYSLT C* C* Build OPNQRYF String and Execute C* C* C MOVEL(P) OPN(1) CMDSTR C @QRYSLT IFNE *BLANKS C CAT SG(1):0 CMDSTR C CAT @QRYSLT:0 CMDSTR C CAT SG(1):0 CMDSTR C ELSE C CAT '*ALL':0 CMDSTR C ENDIF C CAT OPN(2):1 CMDSTR C EXSR $EXCMD C* C CAT @QRYSLT:0 CMDSTR C CAT SG(1):0 CMDSTR C ELSE C CAT '*ALL':0 CMDSTR C ENDIF C CAT OPN(2):1 CMDSTR C EXSR $EXCMD C* C* Open Internally C* C OPEN HD003AL1 C* C* Remove System Messages C* C CALL 'RMVMSG' C* C ENDSR The QRYSLT Edits are shown below these correspond to the fields on the input screen shown above. Points to note are; The Analysis Groups are defined in array CD#, that's the inner loop. The dates are recorded on the file as *ISO and are moved into the string via a character field. C*------------------------------------------------------------* C* $QRYSLT Build QRYSLT expression C*------------------------------------------------------------* C $QRYSLT BEGSR C* ~~~~~~~ ~~~~~ C MOVE *BLANKS @QRYSLT C* C* Status C* C #STSX IFNE '*ALL' C MOVEL(P) '1/CHSTS' @QRYSLT C CAT #COMP:1 @QRYSLT C CAT '"':1 @QRYSLT C CAT #STSX:0 @QRYSLT C CAT '"':0 @QRYSLT C ENDIF C* C Z-ADD 1 #Y C #Y DOWLE 7 C* C #CD(#Y) IFNE '*ALL' C* C @QRYSLT IFNE *BLANKS C CAT '*AND 1/':1 @QRYSLT C CAT #FF(#Y):0 @QRYSLT C ELSE C MOVEL(P) '1/' @QRYSLT C CAT #FF(#Y):0 @QRYSLT C ENDIF C* C CAT '*EQ':1 @QRYSLT C CAT '"':1 @QRYSLT C CAT #CD(#Y):0 @QRYSLT C CAT '"':0 @QRYSLT C* C ENDIF C* C ADD 1 #Y C ENDDO C* C* Range of Dates C* C #FRDAT IFNE '*ALL' C #TODAT ORNE '*ALL' C* C @QRYSLT IFNE *BLANKS C CAT '*AND':1 @QRYSLT C ENDIF C* C* Select Appropriate Date C* .....CL0N01Factor1+++++++Opcode&ExtFactor2+++++++Result++++++++Len++D+H C SELECT C* C #BASD WHENEQ '0' C CAT '1/CHDATE':1 @QRYSLT C #BASD WHENEQ '1' C CAT '1/CHLDAT':1 @QRYSLT C #BASD WHENEQ '2' C CAT '1/CHFDAT':1 @QRYSLT C* C ENDSL C* C* C* .... SELECT Appropriate expression C* ~~~~~~ C SELECT C* C* ........ Range of Dates ..... *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 C* C #FRDAT WHENNE '*ALL' C #TODAT ANDNE '*ALL' C CAT '*EQ':1 @QRYSLT C CAT '%RANGE("':1 @QRYSLT C MOVEL #FRDATISO @CHAR10 C CAT @CHAR10:0 @QRYSLT C CAT '" "':0 @QRYSLT C MOVEL #TODATISO @CHAR10 C CAT @CHAR10:0 @QRYSLT C CAT '")':0 @QRYSLT C* C* ........ From Date only C* C #FRDAT WHENNE '*ALL' C CAT '*GE "':1 @QRYSLT C MOVEL #FRDATISO @CHAR10 .....CL0N01Factor1+++++++Opcode&ExtFactor2+++++++Result++++++++Len++D+H C CAT @CHAR10:0 @QRYSLT C CAT '"':0 @QRYSLT C* C* ........ To Date only C* C #TODAT WHENNE '*ALL' C CAT '*LE "':1 @QRYSLT C MOVEL #TODATISO @CHAR10 C CAT @CHAR10:0 @QRYSLT C CAT '"':0 @QRYSLT C* C ENDSL C* C ENDIF C C* C ENDSR Sorry if my copy and paste is a bit lopsided. The code for closing the files is in a further subroutine. C*------------------------------------------------------------* C* $CLOSE Close the Input File C*------------------------------------------------------------* C $CLOSE BEGSR C* ~~~~~~ ~~~~~ C* Close Internally C* C CLOSE HD003AL1 C* C* Close with CLOF C* C MOVEL(P) CLS(1) CMDSTR C EXSR $EXCMD C* C* Delete Overrides C* C MOVEL(P) DLT(1) CMDSTR .....CL0N01Factor1+++++++Opcode&ExtFactor2+++++++Result++++++++Len++D+H C EXSR $EXCMD C* C* Remove System Messages C* C CALL 'RMVMSG' C* C ENDSR C* ~~~~~ and all commands are executed thru subroutine $EXCMD C*------------------------------------------------------------* C* $EXCMD Execute Command C*------------------------------------------------------------* C $EXCMD BEGSR C* ~~~~~~ ~~~~~ C CALL 'QCMDEXC' C PARM CMDSTR C PARM 1024 CMDLEN 15 5 C* C ENDSR C* ~~~~~ CMDSTR is defined in the D-Spec D CMDSTR DS 1024 Should probably also define CMDLEN here. If this is an interactive application, one other thing the program RMVMSG will remove any system messages which are sent to your message subfile eg. File Opened etc etc blah blah, so as not to freak out the user. Also by changing your job to STSMSG(*NONE) you can suppress the OPNQRYF status messages. I normally save the setting of this is a front-end CL and change it back on exit. I probably need to check out some of the EVAL string handling a bit more but I still like the way CAT does things bit by bit. The code is easier to follow. I would not advise using a massive EVAL statement as it is difficult to edit in *AND and *ORs. Doing it bit by bit also makes the code more open to reusability. Once you've mastered some of the above techniques you will be doing it in your sleep. Build a good skeleton and KISS (Keep it Simple Stupid). One final word of advice. In cases where an item may or may not be selected avoid selections in the string like 'REP *GE " " *AND REP *LE "999"', or defined as a range. Use code to logically include/exclude selections with *AND etc. This other method I have seen and incurs unneccessary overheads, I call this the streetsweeper method. I have been using OPNQRYF since it came out and have done some wonderful things with it. I'm a major fan and I reckon it knocks the socks off SQL performance wise. Hope the techniques shown above help you with your current query. Any further questions post it to the forum. Best of Luck, Michael.

[Guest.Visitor]

Here is an example of RPG/400 for OPNQRYF IQRYSLT DS 1024 IKEYFLD DS 128 C QRYSLT CAT 'QRYSLT':1QRYSLT C QRYSLT CAT '(''':0 QRYSLT * Above two statements build up text QRYSLT(' * below statement is field name C MOVE 'QPOWN1' FNAM C QRYSLT CAT FNAM:1 QRYSLT * Now QRYSLT looks like QRYSLT('QPOWN1 C QRYSLT CAT ' *GT ' QRYSLT * Now QRYSLT looks like QRYSLT('QPOWN1 *GT C QRYSLT CAT '"':1 QRYSLT C QRYSLT CAT FVAL:0 QRYSLT C QRYSLT CAT '"':0 QRYSLT * Now QRYSLT looks like QRYSLT('QPOWN1 *GT "XXXX" -- XXXX is value inside * FVAL field C QRYSLT CAT ''')':0 QRYSLT * Now QRYSLT looks like QRYSLT('QPOWN1 *GT "XXXX"') * Option building C 'OPTION' CAT '(*A':0 #OPT 12 C #OPT CAT 'LL)':0 #OPT * Open Query statement buildup C 'OPNQRYF' CAT OPNID:1 #1024 C #1024 CAT #OPT:1 #1024 C #1024 CAT QRYSLT:1 #1024 * C CALL 'QCMDEXC' C PARM #1024 C PARM LEN