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.
Cleaning messy IBM i Integrated File System (IFS) file names
What is AS400 modernization?
Encrypt IBM i File (Table) Data with no RPGLE changes using SQL
How to read a Data Area (*DTAARA) using IBM i SQL
An Englishman in America
How to update RDi – Rational Developer for IBM ‘i’ Programmers
How to encrypt or hide CL/RPG Source Code in ILE Debug Views
IBM i SQL – Using SUBST to insert data into a string
IBM i User Profile – Special Authorities