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:
wSelectStatmnt = '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
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.
How to rename IFS Files
IFS File system error occurred. Error number 3021?
IBM i Data Obfuscation – Making Data Foggy Murky and Squinty
How to copy IBM i IFS folder
How to rename Fresche (BCD) Presto Library – XL_PRESTO
Using TURNOVER in an IBM i iASP Environment
Preparing to implement IBM i iASP (Independent Auxiliary Storage Pool)
How to move IFS from *SYSBAS into iASP (Independent ASP)
How to refresh the Pitney Bowes CODE1 Address Database on IBM I without using FTP