Count with SQLRPGLE Variable File Name

Been having fun with SQL and RPG ILE this afternoon… the question was “How do I find out if and how many policies exist in one of the reservation files for a given customer code?”

Obviously, we could write a little piece of RPGLE doing a READ LOOP and incrementing the count value for each loop something like this:

clear  countX ;

setll ( mypolicy ) policyfilename;
reade ( mypolicy ) policyfilename;
dow not %eof(filename);
  countX += 1;
  reade ( mypolicy ) policyfilename; 
enddo;

if countX = 0;
  errormsg = 'Policy value ' + myPolicy + ' not found';
endif;

But, for this little blog let’s look at a very simple way of doing this in the SQL RPG.

In it’s simplest form this SQLRPGLE might look like this:

exec sql
select count(*) into :countX from policyfilename where policyFieldName = :myPolicy;

if sqlcode < 0 or sqlcode > 100;
  errormsg = 'Policy value ' + myPolicy + ' not found';
endif;

What if we want to build that select statement in a variable?

We sometimes want to build more complex SELECT statements, with longer more complex selection fields.

Here is the same code block using a variable statements and declaring the select statement as a cursor so we can read the cursor (the constructed select statement) to get the count value for the number of policy lines found.

This is just a code example to show you how to use the cursor function with a variable

The constant value singleQuote simply contains the ‘ character to ease variable construction

Code is just an example of how to do it – not the best way of doing it

A Dynamic SQL statement with a variable file name in SQLRPGLE

dcl-c singleQuote const(''''); // single quote 

SelectStatmnt = 'Select Count(*) from '
 + %trim(policyFileName) + ' where policyFieldName = '
 + singleQuote + %Trim(myPolicy) + singleQuote;

exec sql prepare stmt from :SelectStatmnt ;

exec sql declare myCursor cursor with return to client for stmt;

exec sql open myCursor;

exec sql fetch myCursor into :numberOfClaims;

// Variable "numberOfClaims" now contains the count value
Dsply %char(numberOfClaims); 

exec sql Close C1;

/end-free

Which just goes to show… there is… in fact… more than one way to skin a cat.