CL/400 OPNQRYF returning empty records.

itkr

125 pts.

Tags:

AS/400

AS/400 Records

CL/400

OPNQRYF

hey there,

I got an issue to be corrected. Current CL has and OPNQRYF select which sometimes return blank records. I need to add a condition which should check for such blank query result and by pass a call program.

Can you guys help me in giving a hint on which way I can bypass this blank record scenarios.

Thanks in advance

Kris

Software/Hardware used:
AS/400

Asked: March 18, 2011 12:49 PM Last updated: March 25, 2011 7:56 PM

Answer Wiki

Last updated: March 25, 2011 7:56 PM GMT

NickHutcheson11,485 pts.
History
Contributors
- Ordered by most recent
- NickHutcheson11,485 pts.

Thanks. We'll let you know when a new response is added.

<pre>DCL VAR(&NBRCURRCD) TYPE(*DEC) LEN(10 0)

/* Build records in work file */
call pgmx

/* IF RECORDS EXIST */
RTVMBRD FILE(workfile) NBRCURRCD(&NBRCURRCD)
IF COND(&NBRCURRCD *GT 0) THEN(DO)
/* Process the existing records */
OPNQRYF
ENDDO</pre>

Discuss This Question: 4 Replies

TomLiotta Mar 18, 2011 6:59 PM GMT

OPNQRYF doesn't return records. It only "opens a query file". Records are returned when programs run RCVF or READ statements or when they issue CPYFRMQRYF against the query file that was opened. In order for us to suggest possible reasons records might be "blank", please show code that opens the query file and reads from it. Also, please clarify "...which sometimes return blank records". Does that mean that when a program runs it gets some records that have data and others that don't? Or does it mean that a program can get records with data when it runs one time, but the records are always blank when it runs another time? Do non-blank records ever show up after blank records (assuming the program doesn't crash)? Tom

125,585 pointsBadges:

report
itkr Mar 22, 2011 10:16 AM GMT

yes pardon my language, I meant the same.. Issue is not with the query, needs no change in that. but scenario is during weekends the where condition of that query wont have records to be pulled. This cause the next called pgm fail. So need to change the CL, to have a check condition after this openqry if the select being blank, it should skip the call of a program. I feel this is simple, sad I'm new !! :(

125 pointsBadges:

report
TomLiotta Mar 22, 2011 9:23 PM GMT
The best answer is to fix the program that fails when zero records are selected. That's the actual problem. However, you ask about how to determine if any records are available in the CL before calling the problem program. I'm not sure that there is any good way. One possibility is to try these two commands after your OPNQRYF:
```
CPYFRMQRYF FROMOPNID( myqryf ) TOFILE( QTEMP/qryf ) CRTFILE(*YES) NBRRCDS(1)
POSDBF OPNID( myqryf ) POSITION(*START)
```
The CPYFRMQRY command will try to copy one record into a file in QTEMP. It'll find a record or it won't. If it doesn't find any, there will be messages you can test to skip calling the problem program. The POSDBF command will position the file pointer back to the *START. When you call the problem program, it should be able to read the first record along with any others that follow. Does that make sense? You'll want to run some test CL in a small program in order to see how and when the messages will appear. Tom
125,585 pointsBadges:
report
itkr Mar 23, 2011 2:01 PM GMT

thanks Tom, that was helpful, I will try that...

125 pointsBadges:

report