display file driven by cl

5 pts.
Tags:
CL
SQL
Is there a sample CL/SQL code on how to display a message on the screen based on the last record data in a table. there is a stat feild in my table for eg if stat is 1 then display test1 if stst is 2 then display test2

Answer Wiki

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

Here’s a <i>very basic</i> example:<pre>
pgm

dcl &nbrCurRcd *dec ( 10 0 )
dcl &nbrDltRcd *dec ( 10 0 )
dcl &LastRcd *dec ( 10 0 )

dcl &CntRcdRd *dec ( 10 0 )
dcl &Cnt_c *char 11

dcl &Rpy *char 1

dcl &f_EOF *lgl value( ’0′ )

dclf CUSTCDT

rtvmbrd CUSTCDT +
nbrcurrcd( &nbrCurRcd )

ovrdbf CUSTCDT position( *RRN &nbrCurRcd )

dountil ( &f_EOF )

chgvar &CntRcdRd ( &CntRcdRd + 1 )
rcvf
monmsg ( cpf0864 ) exec( do )
chgvar &f_EOF ’1′
sndusrmsg ‘This is the end of the file…’ +
tomsgq( *EXT ) +
msgrpy( &Rpy )
enddo

enddo

/* Check for a credit limit… */

chgvar &Cnt_c &CntRcdRd

if ( &CDTLMT *eq 9999 ) do
sndusrmsg ( ‘This customer has the maximum credit limit +
— reads:’ *bcat &Cnt_c ) +
tomsgq( *EXT ) +
msgrpy( &Rpy )
enddo
else do
sndusrmsg ( ‘This customer has a restricted credit limit +
— reads:’ *bcat &Cnt_c ) +
tomsgq( *EXT ) +
msgrpy( &Rpy )
enddo

return

endpgm</pre>
The program needs a file named CUSTCDT (Customer Credit) in the library list for the compile. I created a copy with:<pre>
CRTDUPOBJ OBJ(QCUSTCDT) FROMLIB(QIWS) OBJTYPE(*FILE)
TOLIB( mylib ) NEWOBJ(CUSTCDT) DATA(*YES)</pre>
Almost all AS/400s have library QIWS with the example file QCUSTCDT. You should be able to create your own copy for testing.

The example program retrieves the Number of Current Records (&nbrCurRcd) from the file. (Actually from the *FIRST member of the file.) It uses that number to <i>position</i> to a <i>relative record number</i>. It reads from that point in the file until end-of-file is signaled.

After it falls out of the read-loop, the value that is left in the &CDTLMT (Credit Limit) variable is tested. In this program’s logic, if the credit limit is 9999, it sends a message that this last record has the maximum credit limit. If it’s not 9999, then the message is that it’s a restricted limit.

Note that this kind of program can’t simply position to the relative record number (RRN) that is equal to &nbrCurRcd. There might be deleted records mixed in. The program must read with RCVF until it actually learns that the last active record was read.

And it can’t retrieve &nbrCurRcd along with Number of Deleted Records and add those two together to find a position in the file. Maybe the records at the end of the file have been deleted — end-of-file might be signaled on the very first RCVF without ever actually reading any records.

Now, whether you want to use SNDUSRMSG to ‘send a message’ to the screen is up to you. You might prefer SNDPGMMSG or a few other possibilities. It will depend on a few circumstances that you haven’t described. You might simply need to try variations to see what works. I used SNDUSRMSG as an *INQ (inquiry) message and asked for a reply (&Rpy). I don’t do anything with the reply; it’s there just to force some interaction with the user.

Overall, reading particular records from files isn’t a very good fit for CL. It’s a <i>Control Language</i> not an application language. You have to be prepared to do some unusual coding if you’re going to stick with CL.

Tom

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Teandy
    You may find what you need here:
    5,860 pointsBadges:
    report
  • Teandy
    Let's try that again: http://www.mcpressonline.com/programming/apis/the-api-corner-sending-non-error-related-messages-from-an-application-program.html
    5,860 pointsBadges:
    report
  • bvining
    Vendor Response If you have Control Language for Files (CLF) installed then the following program should do the trick. In my example *Last is determined by relative record number. If the file is keyed then the Access Method parameter needs to be specified on the Open File using CLF (OpnFCLF) command.
    Pgm                                                    
    DclFCLF    StatFile                                    
    OpnFCLF    StatFile                                    
    ReaDRcdCLF StatFile Type(*Last)                        
    If         Cond(&Stat = '1') Then( +                   
                 SndUsrMsg Msg(TEST1) MsgType(*Info))      
    Else       Cmd(SndUsrMsg Msg(TEST2) MsgType(*Info))    
    CloFCLF    StatFile                                    
    EndPgm                                                 
    
    More information on CLF can be found here. End vendor response. Bruce Vining
    6,465 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following