.st0{fill:#FFFFFF;}

OPNQRYF – Short explanation with some samples. 

 January 23, 2009

By  NickLitten

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

NickLitten


IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Shameless Trekkie, Belligerent Nerd, Englishman Abroad and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and remember: If at first you don't succeed then skydiving probably isn't a hobby you should look into.

Nick Litten

related posts:

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"cff50":{"name":"Main Accent","parent":-1},"a344d":{"name":"Accent Transparent","parent":"cff50"}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"cff50":{"val":"var(--tcb-skin-color-0)"},"a344d":{"val":"rgba(46, 138, 229, 0.85)","hsl_parent_dependency":{"h":210,"l":0.54,"s":0.78}}},"gradients":[]},"original":{"colors":{"cff50":{"val":"rgb(0, 178, 255)","hsl":{"h":198,"s":1,"l":0.5}},"a344d":{"val":"rgba(0, 178, 255, 0.85)","hsl_parent_dependency":{"h":198,"s":1,"l":0.5}}},"gradients":[]}}]}__CONFIG_colors_palette__

Get In Touch

I’m always looking for awesome input, feedback and critique!

>