Comparing RPG and SQL Functionality

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:

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

RPG Built-In Function

SQL Built-In Function(s) (or rough equivalent)

%ABS – Absolute Value of
Expression

ABS

%CHAR – Convert to
Character Data

CAST, CHAR, VARCHAR

%DATE – Convert to Date

CAST, DATE

%DAYS – Number of Days

DAYS (duration)

%DEC – Convert to Packed
Decimal Format

CAST, DECIMAL

%DECH – Convert to Packed
Decimal Format with Half Adjust

DECIMAL and ROUND

%DIFF – Difference Between
Two Date, Time, or Timestamp Values

TIMESTAMPDIFF

%DIV – Return Integer
Portion of Quotient

Combination of INT
functions and division using / symbol

%FLOAT – Convert to
Floating Format

CAST, FLOAT, DOUBLE, REAL

%GRAPH – Convert to
Graphic Value

CAST, GRAPHIC, VARGRAPHIC

%HOURS – Number of Hours

HOURS

%INT – Convert to Integer
Format

CAST, INT

%INTH – Convert to Integer
Format with Half Adjust

INT and ROUND

%LEN – Get or Set Length

LENGTH (get only)

%MINUTES – Number of
Minutes

MINUTES (Duration)

%MONTHS – Number of Months

MONTHS (Duration)

%MSECONDS – Number of
Microseconds

MICROSECOND (Duration)

%NULLIND – Query or Set
Null Indicator

IS NULL (query only), SET var=NULL

%REM – Return Integer
Remainder

MOD

%REPLACE – Replace
Character String

REPLACE (with some
caveats)

%SCAN – Scan for
Characters

POSITION, POSSTR

%SECONDS – Number of
Seconds

SECONDS (Duration)

%SQRT – Square Root of Expression

SQRT, POWER

%SUBDT – Extract a Portion
of a Date, Time, or Timestamp

DAY, YEAR, MONTH, HOUR,
MINUTE, etc.

%SUBST – Get Substring

SUBSTR

%TIME – Convert to Time

CAST, TIME

%TIMESTAMP – Convert to
Timestamp

CAST, TIMESTAMP

%TRIM – Trim Blanks at
Edges

TRIM, STRIP

%TRIML – Trim Leading
Blanks

LTRIM

%TRIMR – Trim Trailing
Blanks

RTRIM

%UCS2 – Convert to UCS-2
Value

CAST(x AS VARGRAPHIC(n)
CCSID 13488)

%XLATE – Translate

TRANSLATE

%YEARS – Number of Years

YEARS

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

RPG Op-Code

Possible SQL Replacement

ADD – Add

+ operator

ADDDUR – Add Duration

Date or Timestamp +
duration value

CALL – Call a program

CALL

CALLP – Call a prototyped
procedure or program

Create Procedure
(External) / CALL

CAT – Concatenate two
strings

|| operator, CONCAT
function

CHAIN – Random retrieval
from a file

SELECT INTO, SELECT …
FETCH FIRST 1 ROW ONLY

COMMIT – Commit

COMMIT

COMP – Compare

=, <=, <, >,
>=, <>

DELETE – Delete Record

DELETE statement with
WHERE criteria or positioned delete using a cursor

DIV – Divide

/ operator

DOxxx – Do

WHILE, REPEAT

EVAL – Evaluate expression

SET

GOTO – Go to

GOTO

IF, ELSE

IF THEN, ELSE

ITER – Iterate

ITERATE

MONITOR, ON-ERROR – Begin
a monitor group

DECLARE xxxx HANDLER

MOVE, MOVEL, EVALR – Move,
Move Left, etc.

Combination of SET and
built-in functions

MULT – Multiply

* operator

MVR – Move remainder

MOD function

READ, READP – Read, Read
Prior

DECLARE CURSOR, SELECT and
FETCH NEXT or FETCH PRIOR

READE, READPE – Read
Equal, Read Prior Equal

DECLARE CURSOR, SELECT and
FETCH NEXT or FETCH PRIOR with appropriate WHERE criteria

ROLBK – Roll back

ROLLBACK

SCAN – Scan String

POSSTR built-in function

SELECT, OTHER – Begin a
Select Group

CASE

SUBDUR – Subtract duration

Date or Timestamp –
duration value

TIME – Retrieve time and
date

Current_Date, Current_Time, Current_Timestamp registers

UPDATE – Modify an
existing record

UPDATE statement with
WHERE criteria or positioned update using a cursor

WRITE – Write a record

INSERT INTO

XLATE – Translate

TRANSLATE function

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

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search