A Bevy of BIFs: %Dec to the Rescue

Published: May 20, 2009

by Susan Gantner

More and more in RPG applications these days, it seems we need to process data that comes from "the dark side." Translation: from a non-i system. This data could be coming from a browser screen via an RPG CGI program, from a CSV (comma-separated values) flat file, from an XML document, or myriad other ways. One thing these dark sources often have in common is that data that should be numeric often isn't--at least not by RPG's definition. So common is the issue of invalid numeric data that one of the recommendations from IBM on using the XML-INTO operation code is to define the fields in the DS to receive the XML data as character and then translate them into numeric fields using RPG logic later.

I've seen several examples of some fairly complex logic to accomplish this translation from character to numeric, but many times the use of %Dec does the job much more simply. With a little help from %Xlate and Monitor, %Dec can handle most of the situations that the more complex logic would have done.

First, let's look at the basics of the %Dec built-in function. The syntax for converting from character fields looks like the following.

%DEC(character expression : length : decimal places)

Notice that when converting from character data to numeric, the length/precision and decimal places are not optional.

The %Dec built-in can also be used to control the precision of numeric expressions and also to convert date/time/timestamp data. The latter is very useful for moving database data back into numeric form after it had been translated to date/time/timestamp form for use with RPG's native date-handling facilities. But our main focus here will be for converting data that should be numeric--but isn't quite--so that we can use and/or store it appropriately in RPG.

Of course, when the character data is clean--by RPG standards--the process is very simple, as illustrated in this example:

D CharField1      S              14a   Inz('     1525.95-') 

   NumField = %Dec(CharField1 : 9 : 2);

RPG will accept and properly translate positive (+) or negative (-) signs either at the beginning or end of the numeric data. A decimal point can be specified and blanks are allowed anywhere in the expression/field. However, it won't accept currency symbols, thousands separators, and asterisks.

So how can we easily deal with those characters that could very feasibly occur in "numeric" fields? An easy way is to translate them into something that is allowed, such as spaces, using %XLate. Note that one of the powers of using functions is that often one can be used as a parameter value to another, so we're not forced to do the old RPG/400 technique requiring temporary variables. If you're not familiar with the details of %XLate, check out this tip from Jon Paris: A Bevy of BIFs: %XLATE and %REPLACE. An example of using it with %Dec is shown below.

D CharField2      S             14a   Inz('   $1,525.95-')

   NumField = %Dec( %XLate('$,*' : '   ' : CharField2 ) : 9 : 2);

So far, so good. But what if something else appears in the supposedly numeric value that we haven't anticipated? After all, it happens. Some weird unexplainable character appears in the field or some unsupported way of designating a negative value (such as parentheses) is used. In that case, %Dec will throw a status code 105 and error message RNX0105 (A character representation of a numeric value is in error.)

That's where our friend Monitor comes in. If you monitor for the error on the %Dec operation, you can then take appropriate action; whatever that action is for the situation where you've received bad data. The resulting code would then look something like the following:

  D CharField3      S             14a   Inz('  $1,5Q25.95-')

     Monitor;

        NumField = %Dec(  %Xlate('$,*' : '   ' : CharField3 ) : 9 : 2);

     On-Error 105;
          // Error handling logic goes here
     EndMon;

Your error handling logic might simply log the transaction as an error for follow up later and move on to the next logical piece of data. In some cases, you may want to attempt to translate some other characters, such as various indications of negative values, such as parentheses or CR. For those situations, you'll likely find that functions such as %Check or %Scan (see A Bevy of BIFs: %SCAN and %CHECK) and %Replace (see A Bevy of BIFs: %XLATE and %REPLACE) are useful.

Of course, hopefully we have all learned by now that hard-coding constant values in our logic is not productive during maintenance, so a better way of coding the logic above might look like this:

D NumberEdits     C                   '$,*'
D Blanks4Xlate    C                   '   '
D BadNumData      C                   105

   Monitor;

      NumField = %Dec( %Xlate(NumberEdits : Blanks4Xlate :
                  CharField3 ) : 9 : 2);

   On-Error BadNumData;
      // Error handling logic goes here
   EndMon;

By the way, the technique of using Monitor to find the "problem fields" for you is typically more efficient than coding extensive bad data detection and handling logic yourself before using %Dec. This is because it's highly likely that the vast majority of those so-called numeric values really are numeric, especially after taking care of some common issues, such as thousands separators and currency symbols. So there's no need to run the bad data detection logic for all those fields that are good.

As a matter of fact, if you think most of the data won't even contain the currency symbol, thousands separator, etc., you could make it even more efficient by moving the %XLate function to the error handling routine instead of imbedding it into the %Dec function. Nested Monitor blocks work well for this. It would look something like this:

     Monitor;

        NumField = %Dec( CharField3 : 9 : 2 );

     On-Error BadNumData;

        Monitor;
           NumField = %Dec( %Xlate(NumberEdits : Blanks4Xlate :
                       CharField3 ) : 9 : 2);
        On-Error BadNumData;
           // Error handling logic goes here
        EndMon;

     EndMon;

So now you have a simple way to get those so-called numeric values into your numeric fields in your RPG programs, courtesy of a few RPG built-in functions.

Susan Gantner is one of the most respected System i gurus in the world and is one of the co-founders of System i Developer, an organization dedicated to RPG, DB2, and other relevant software technologies for the System i platform that hosts the new RPG & DB2 Summit conference. Gantner, who has worked in IBM's Rochester and Toronto labs, left IBM to focus on training OS/400 and i5/OS shops on the latest programming technologies. She is also a regular speaker at COMMON and other user groups. Send your questions or comments for Susan to Ted Holt via the IT Jungle Contact page.

RELATED STORIES

A Bevy of BIFs: %CHAR, %EDITC and %EDITW

A Bevy of BIFs: Dealing with a Bad Date

A Bevy of BIFs: %XLATE and %REPLACE

A Bevy of BIFs: %SCAN and %CHECK

A Bevy of BIFs: Getting a Date is Easy with %Date

A Bevy of BIFs: Look Up to %LookUp

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