June 17

0 comments

Getting a count with SQLRPGLE Variable File Name

By NickLitten

June 17, 2015

#count, #file, #RPGLE, #SQLRPGLE, #variable

Been having fun with SQL and RPGLE this afternoon… the question was “how do I find out if and how many Policy numbers exist in one of the Policy files at any given time?”

So, this meant using a variable file name in the RPG code, with a variable policy number. Took some playing around but found a solution that worked great and it looks like this:

A Dynamic SQL statement with a variable file name in RPGLE

RPG Code Snippet SQLRPGLE Variable File NamewSelectStatmnt = 'Select Count(*) '
 + 'From ' + %Trim(wInpFileName)
 + cBlank
 + ' Where LsPol# = '
 + cSqlQuote
 + %Trim(IwPol#)
 + cSqlQuote;

exec sql prepare stmt from :wSelectStatmnt ;

exec sql declare C1 cursor with return to client for stmt;

exec sql open C1;

exec sql fetch C1 into :wNumClaims ;

Dsply wNumClaims; 

exec sql Close C1;

/end-free
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips

>

Snug CBD

 20% Discount

I have partnered with SNUG CBD givING you Organic CBD
20% discount code "NL20"