Comparing RPG and SQL Functionality

Published: July 22, 2009

Hey, Mike:

Is there a book on how RPG operation codes and built-in functions translate to SQL? I'm trying to leverage my RPG coding skills to write logic in a DB2 stored procedure. That is, I know exactly what to do in RPG, but I don't know the SQL equivalent.

To determine the length of a city name, I would use RPG code like this:

Eval CityLength = %Len(%Trim(City))

How would I do the same in a stored procedure SET statement? I have used the following to find a field length, but my field is an input parameter to the stored procedure.

SELECT length(trim(bsrequser)) from bs2hist

Thanks for any help you can give.

--Doc

Hey, Doc:

You're closer than you think. The SET statement in a DB2 stored procedure is almost identical to the RPG EVAL statement. Here is an example of how to do the operation using SET:

Create Procedure MyProcedure
(@City IN Char(60))
Language SQL
Begin
    Declare @CityLength Int Not Null Default 0;

    Set @CityLength=Length(Trim(@City));

.....

For your information, you can also include a variable (including a parameter variable) in a SELECT column list, if you want to do so (although I use SET most of the time):

SELECT Length(Trim(@City))
  Into @CityLength 
  From SYSIBM/SYSDUMMY1;

While I don't know of a specific book to help you translate directly from RPG to SQL, it is fairly simple to move from free-form RPG to SQL just by studying the manuals:

• RPG Reference
• SQL Reference

For instance, in my "glory days" of RPG programming, I would study the list of functions and op-codes to know as many capabilities of the language as possible. Then, when I moved into the SQL world, I did the same thing and eventually noticed many similarities. If you study the list of built-in functions in RPG, you can find equivalents for many of them by reviewing the built-in scalar functions in the SQL manual.

The tables below show a quick cross-reference between RPG and SQL. Table 1 contains a list of often used RPG built-in functions and the approximate SQL equivalent function or functions. Table 2 shows common RPG operation codes (op-codes) and possible equivalent SQL statements or functions.

Keep in mind that just because there isn't a direct equivalent between the languages doesn't mean that SQL can't accomplish the same thing. It's often just a matter of learning to think differently. Nowhere is this "thinking difference" more apparent between RPG and SQL than thinking about working with individual records in RPG and thinking in terms of sets of rows in SQL.

Sometimes it may take two or more steps in one language over the other. Sometimes, SQL is just lacking ability to work with things like printer files and data areas. But don't forget SQL can use the strengths of RPG by accessing RPG logic as a function or stored procedure.

In the end it just takes practice, but the productivity reward for mastering SQL is worth the effort.

--Michael Sansoterra

Table 1: Comparison of RPG built-in functions and possible SQL substitutes.

Table 2: Comparison of RPG operation codes and possible SQL substitutes.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot