Getting a count with SQLRPGLE Variable File Name

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