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.
Getting a count with SQLRPGLE Variable File Name
Cleaning up a string of comma separated account numbers in RPGLE
RPG Divide and Move Remainder – DIV MVR in ILE RPGLE /FREE
Skeleton RPGLE Trigger Program
Update IBM i IFS Objects Data Change Timestamp
How to get a list of all files in an IFS folder
Edit MENU with IBM i RDI
What is AS400 modernization?
Encrypt IBM i File (Table) Data with no RPGLE changes using SQL