Getting a count with SQLRPGLE Variable File Name

IBM i

Jun 17

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
Follow

About the Author

IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd 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 in the words of the most interesting man in the world: Stay thirsty my friend.