Executing Dynamic Calculations with Embedded SQL

November 10, 2004 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

Let Me Out of Here!

November 10, 2004 Hey, Ted

Having read your article “Subprocedures: Better than Subroutines,” I have been inspired to use subroutines less and subprocedures more in RPG programs. However, I have come up against a problem that I can’t solve.

In many of my programs I include a clean-up subroutine that runs when the program ends, whether the program ends normally or abnormally. The subroutine includes various house-keeping tasks, sets on the LR indicator, and executes a RETURN operation. You probably know what happens when I implement such a routine in a subprocedure. Instead of returning to the calling program, control returns to the calling routine

More Conditional Sorting with SQL

November 10, 2004 Hey, Ted

Your tip “Conditional Sorting with SQL” is one cool tip! May I add two more examples?

My examples are in RPG, but I assume they will work with any language. In both examples, I use host variables to control the sort.

Host variable SortOption controls the sort in the first example. If host variable SortOption has a value of 1, the data is retrieved in order by customer name. If two customers have the same name, they are retrieved in customer number sequence. If SortOption has any other value, the data is retrieved in customer number sequence.

D SortOption      S