Executing Dynamic Calculations with Embedded SQL

by Michael Sansoterra

The code for this article is available for download.

Every so often I encounter an application where an advanced user wants the ability to maintain a formula. Further, the formula may change every so often, so that building the formulas into the program is undesirable. This is a difficult situation in the RPG world, since the RPG language has no ability to evaluate a string expression at runtime.

Here's an example:

Formula='Qty * Price * (1 + Case When Qty > 100 Then .10 Else 0 End)';

Fortunately, SQL can handle this dynamic calculation.

This calculation can be done by following these steps:

1. Build a string containing an SQL column expression.
   
   
2. Replace the predefined parameters in the string with their runtime values. (Users must know beforehand a list of variables they're able to use in a formula.)
   
   
3. Embed the column expression in a SELECT statement that returns a single row.
   
   
4. Use SQL's Prepare statement to convert the SELECT statement into an executable form.
   
   
5. Open a cursor from the prepared SELECT statement.
   
   
6. Fetch the data into a host variable.
   
   
7. Close the cursor.

These steps are outlined in RPG program CALCR. The program uses subprocedure Calculator to dynamically calculate a value from a string expression. Here's a sample of how the Calculator subprocedure can be used:

/Free
    Amt=23.50;
    Qty=10;
    Expr='AMT*CASE WHEN QTY>5 THEN .1 ELSE .05 END';
    Expr=Replace(Expr:'AMT':%Char(AMT));
    Expr=Replace(Expr:'QTY':%Char(QTY));

    // Discount evaluates to 2.35  (23.50 * .01)
    Discount=Calculator(Expr);

    Amt=50.00;
    Qty=1;
    Expr='AMT*CASE WHEN QTY>5 THEN .1 ELSE .05 END';
    Expr=Replace(Expr:'AMT':%Char(AMT));
    Expr=Replace(Expr:'QTY':%Char(QTY));

    // Discount evaluates to 2.50  (50.00 * .05)
    Discount=Calculator(Expr);

/End-Free
 

There are a few things to note.

• Typically, dynamic formulas are stored in a table, a data area, or an IFS file. The formula is only hard-coded in the sample program for the sake of illustration.
  
  
• Any predefined variables a user might want to use will have to be available to the RPG program at calculation time.
  
  
• Calculator's result returns a generic DEC(15,5) value. This can be expanded to fit your needs if a greater precision or scale is required. On V5R3 systems, the maximum precision has been increased to 63! For extremely large or small numbers, create a version of Calculator that returns the double data type.
  
  
• The SQL Prepare statement is expensive, in terms of performance, so don't overuse it.
  
  
• Any SQL column functions can be used in your expressions, such as random number generation, date calculations, trigonometric functions, and CASE expressions.
  
  
• Any expression that returns a NULL will create an error. If you're unhappy with this behavior, it can be avoided by always using the IFNULL function to force the result to zero, or by adding an indicator variable to the program.

Sample program CALCR shows how easily these calculations can be done. Simply pass a column expression (conforming to DB2's SQL syntax) as a parameter on the command line, and watch the program return the result. The program demo can accept two substitution variables QTY (replaced by a value of 5) and AMT (replaced by a value of 23.50).

Note that the expression parameter is only 30 characters long.

CALL CALCR PARM('3+3')    /* Displays 6 */

CALL CALCR PARM('5*QTY')  /* Displays 25 */

CALL CALCR PARM('ROUND(PI() * 2,2)')  /* Displays 6.28 */

As you can see, SQL gives RPG the same power that the Eval function has given to Access, Visual Basic, VBScript, and JavaScript programmers: that is the ability to dynamically evaluate complex expressions without technical programming.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Click here to contact Michael Sansoterra by e-mail.