another snippet from an old document I found in a dim dark and hazy corner of an even older HDD… rather than let it disintegrate, here it is migrating to the web just in the (extreme) off chance that it may be useful to someone out there. Hey.. if YOU are reading this then its done its job 😉
CL-PROGRAM
FUNCTION…..: RETRIEVE/SELECT DATA WITH THE CL-COMMAND OPNQRYF.
ILLUSTRATED IN SAMPLES 1 – 3.
TASK………: RETRIEVE USERINFO ON FIELDS FROM THE USRPRF-FILE.
USE THE CL-COMMAND DSPUSRPRF *ALL OUTPUT(*OUTFILE) FILE(LIB/QRYSLTPF). LIB IS YOUR OWN TEST-LIBRARY. THE PROGRAM ONLY USES 2 FIELDS TO AVOID CONFUSION. IN THIS PROGRAM THE TEST-LIBRARY IS JPHLIB.
INPUT……..: DB-FILE: QRYSLTPF
FIELDS: UPUPRF 10 A
UPUID 10 P0
DISCLAIMER…: THE DATA RETRIEVED AND THE COMBINATION OF FIELDS IS COMPLETELY NONSENSE AND INTENDED ONLY TO ILLUSTRATE THE USE OF THIS COMMAND. CREATE BETTER EXAMPLES ON YOUR OWN !!
CREATE A FILE IN YOUR TESTLIBRARY CALLED BRUG. THIS IS THE FILE THAT RECIEVES OUTPUT-DATA FROM THE OPNQRYF.
OUTPUT…….: DB-FILE: BRUG
FIELDS: UPUPRF 10 A
UPUID 10 P0
OPENID IN THE OPNQRYF-STATEMENT IS YOUR REFERENCE TO THE INTERNAL OPNQRYF-OUTPUT. USE THIS NAME AS FROM-FILE IN THE FINAL CPYFRMQRYF WHERE YOU RETRIEVE THE SELECTED DATA TO A PHYSICAL FILE.
TIP……….: BE ABSOLUTELY SURE TO USE THE RIGHT NUMBER OF QUOTES ( ‘ ) WHEN YOU DEFINE THE SELECT-STATEMENT. ALL CHAR-VARIABLES IN THE OPNQRYF SELECT-LINE MUST BE EMBEDDED IN TRIPLE-QUOTES AND *CAT:
''' *CAT &CHARVAR *CAT '''
OR
"' *CAT &CHARVAR *CAT '"
PGM
DCL VAR(&USER) TYPE(*CHAR) LEN(10)
DCL VAR(&NR) TYPE(*DEC) LEN(10 0)
DCL VAR(&EX) TYPE(*DEC) LEN(1 0)
DCL VAR(&NRALF) TYPE(*CHAR) LEN(10)
DCL VAR(&X) TYPE(*CHAR) LEN(1) VALUE(' ')
DCLF FILE(QRYSLTDF) RCDFMT(*ALL)
/* THE VAR X HELPS TO LEAVE THE LIBRARY-LIST UNCHANGED */
/* WHEN THE PROGRAM HAS FINISHED PROCESSING. */
ADDLIBLE LIB(JPHLIB)
MONMSG MSGID(CPF2103) EXEC(CHGVAR VAR(&X) VALUE('X'))
/* PROMPT FOR NAME OG ID AND TYPE OF EXAMPLE */
CHOISE: SNDRCVF RCDFMT(F0)
/* QRYSLT ONLY OPERATES WITH ALFA-VARIABLES. THE NUME- */
/* RIC VAR. &NR IS CONVERTED TO CHAR. &NRALF */
/* USED IN SAMPLE 3. */
CHGVAR VAR(&NRALF) VALUE(&NR)
/* F3 WAS PRESSED ON THE SCREEN */
IF COND(&IN03 = '1') THEN(GOTO CMDLBL(END))
IF COND(&EX = 1) THEN(GOTO CMDLBL(ONE))
IF COND(&EX = 2) THEN(GOTO CMDLBL(TWO))
IF COND(&EX = 3) THEN(GOTO CMDLBL(THREE))
/*****************************************************************/
/* SAMPLE ONE: CHAR CONSTANT AND NUM CONSTANT */
/*****************************************************************/
ONE:
OPNQRYF FILE((JPHLIB/QRYSLTPF)) +
QRYSLT(' +
(UPUPRF *EQ ''JPH'') +
*AND +
(UPUID *EQ 338) +
') +
OPNID(BRUG)
GOTO CMDLBL(OUT)
/*****************************************************************/
/* SAMPLE TWO: CHAR VARIABLE AND NUM CONSTANT */
/*****************************************************************/
TWO:
OPNQRYF FILE((JPHLIB/QRYSLTPF)) +
QRYSLT(' +
(UPUPRF *EQ ''' *CAT &USER *CAT ''') +
*AND +
(UPUID *EQ 338) +
') +
OPNID(BRUG)
GOTO CMDLBL(OUT)
/*****************************************************************/
/* SAMPLE THREE: CHAR VARIABLE OG NUM VARIABLE. */
/* DIGITS IS A OPNQRYF KEYWORD THAT CONVERTS */
/* A FIELD FROM NUMERIC TO ALFA (CHAR.) */
/*****************************************************************/
THREE:
OPNQRYF FILE((JPHLIB/QRYSLTPF)) +
QRYSLT(' +
(UPUPRF *EQ ''' *CAT &USER *CAT ''') +
*AND +
(%DIGITS(UPUID) *EQ ''' *CAT &NRALF *CAT ''') +
') +
OPNID(BRUG)
GOTO CMDLBL(OUT)
/*****************************************************************/
/* MAKE A COPY OF THE OPNQRY OUTPUTFILE TO THE PF BRUG */
/*****************************************************************/
OUT: CPYFRMQRYF FROMOPNID(BRUG) TOFILE(JPHLIB/BRUG) +
MBROPT(*ADD)
CLOF OPNID(BRUG)
DLTOVR FILE(*ALL)
GOTO CMDLBL(CHOISE)
/* LIBRARY-LIST IS RESTORED */
END: IF COND(&X = ' ') THEN(RMVLIBLE LIB(JPHLIB))
RCLRSC
RETURN
___FCKsi___122 ENDPGM
___FCKpd___156
OPNQRYF and Quotation Marks
I have to create a record selection expression for a character field that can have values like 4″ in it. When an expression like ATSIN *EQ “4”” occurs, the Open Query File (OPNQRYF) command gives an error.
Is there a way around it?
—
You’ll have to double something in your code. In your example, you surround the literal 4″ with double quote marks ( ” ). You need to change the one double quote mark following the number 4 to two double quote marks. Your query selection string will look like this:
ATSIN *EQ “4”””
The other way to do this is to use two single quote marks ( ‘ ) to delimit the literal, like this:
ATSIN *EQ ”4″”
That’s single quote single quote the numeral four a double quote single quote single quote.
Here’s some CL code that illustrates this concept. &DESC is the field holding the user input. &DWORK holds the user input with doubled quote marks.
DCL &DESC *CHAR LEN( 25)
DCL &DWORK *CHAR LEN( 50)
DCL &QRYSLT *CHAR LEN(256)
DCL &DBLQUOTE *CHAR LEN( 1) VALUE(‘”‘)
DCL &INX *DEC LEN(3 0)
DCL &OUTX *DEC LEN(3 0)
/* Get value of &DESC using some input method here */
/* Begin routine to change a double quote to two */
/* double quotes so OPNQRYF selects records properly */
CHGVAR &INX 1
CHGVAR &OUTX 1
NextChar:
CHGVAR %SST(&DWORK &OUTX 1) %SST(&DESC &INX 1)
IF (%SST(&DESC &INX 1) *EQ &DBLQUOTE) DO
CHGVAR &OUTX (&OUTX + 1)
CHGVAR %SST(&DWORK &OUTX 1) (&DBLQUOTE)
ENDDO
CHGVAR &INX (&INX + 1)
CHGVAR &OUTX (&OUTX + 1)
IF (&INX *LE 25) THEN(GOTO NextChar)
/* End routine to double quotation marks */
CHGVAR &QRYSLT VALUE(‘PMDESC *CT’ *bcat +
&DblQuote *cat +
&DWork *tcat +
&DblQuote)
OPNQRYF FILE((PARTS)) QRYSLT(&QRYSLT)
— Ted
OAGRPG1 :
**************************************************************
H/TITLE OAG TEST PROGRAM.
*
FOAGPF1 O E DISK
*
C Z-ADD1 COUNT 50
C COUNT DOWLE3
C MOVE COUNT SLNO
C WRITEREC1
C ADD 1 COUNT
C ENDDO
*
C SETON LR