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