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
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