Home > IT Answers > AS/400 > CL/400 OPNQRYF returning empty records.
itkr
125 pts.
 CL/400 OPNQRYF returning empty records.
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
UPDATED: March 25, 2011  7:56 PM
RELATED QUESTIONS
 
  Help
 Approved Answer - Chosen by itkr (Question Asker)

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

ANSWERED:  Mar 22, 2011  9:23 PM (GMT)  by itkr
 
Other Answers:

<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>

Last Wiki Answer Submitted:  March 25, 2011  7:56 pm  by  NickHutcheson1   1,380 pts.
Latest Answer Wiki Contributors:  NickHutcheson1   1,380 pts.
To see other answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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

TomLiotta
 107,995 pts.
 

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 !! :(

itkr
 125 pts.
 

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

itkr
 125 pts.