The QECCVTEC and QECEDT APIs offer flexibility in terms of providing run-time editing of numeric values.

Back on May 5, Jeff Y. posed the following question on midrange-l (a popular i-related mailing list that you can access here):

"Does anyone have a UDF (or other SQL method) of taking a numeric field and returning a character value representing the value as edited via an edit code or edit word in RPG? I know that if I use CAST/SUBSTR/CONCAT that I can do this, but was looking for a neater way. Needs to be supported on V5R4 or earlier."

It was recommended that he create a user-defined function (UDF) where the UDF could then use the RPG %editc (or %editw) built-in function to format the numeric field. This approach certainly works, but it has a consideration/limitation that I consider to be a real pain—namely, that the RPG built-ins require that the numeric variable and edit code/edit word be known at compile time. This restriction effectively requires that the UDF developer decide in advance the precision (total number of digits) and scale (number of digits following the decimal point) of the numeric values to be edited, along with all of the various edit codes that a user might want to apply to each combination of precision and scale. Not an impossible task, but there would certainly be a large number of potential combinations that you may need to code for.

An alternative solution would be to create a UDF utilizing the Convert Edit Code (QECCVTEC) and Edit (QECEDT) APIs. The QECCVTEC API, documented here for V5R4, allows you to convert an edit code to an edit mask. Unlike the RPG %editc built-in function, the QECCVTEC API allows you to specify the edit code, precision, and scale all as run-time variables. The QECEDT API, documented here for V5R4, can then be used to transform a given numeric value to an edited character form of the number using the previously generated edit mask. Below is the source for such a UDF, named EDITC, implemented as an exported *SRVPGM function.

h nomain                                                         

dEditC            pr           256a   varying                    

d NbrIn                         31p 9 const                      

d Precision                     10i 0 const                      

d Scale                         10i 0 const                      

d EditCode                       1a   const varying              

dEdit             pr                  extpgm('QECEDT')           

d RcvVar                       256a                              

d LenRcvVar                     10i 0 const                      

d NbrToEdt                      31a   const options(*varsize)    

d NbrClass                      10a   const                      

d Precision                     10i 0 const                      

d EdtMsk                       256a   const                      

d LenEdtMsk                     10i 0 const                      

d ZeroSupr                       1a   const                   

d QUSEC                               likeds(QUSEC)           

 /copy qsysinc/qrpglesrc,qusec                                

pEditC            b                   export                  

dEditC            pi           256a   varying                 

d NbrIn                         31p 9 const                   

d Precision                     10i 0 const                   

d Scale                         10i 0 const                   

d EditCode                       1a   const varying           

dCvtEdtCd         pr                  extpgm('QECCVTEC')      

d EdtMsk                       256a                           

d LenEdtMsk                     10i 0                         

d LenEdtVal                     10i 0                         

d ZeroSupr                       1a                             

d EditCode                       1a   const                     

d FillChr                        1a   const                     

d Precision                     10i 0 const                     

d Scale                         10i 0 const                     

d QUSEC                               likeds(QUSEC)             

dCpyNv            pr                  extproc('_LBCPYNV')      

d Rcv                                 like(Nbr)                

d RcvAtr                              const like(ZonedOutput)  

d Src                                 const like(NbrIn)        

d SrcAtr                              const like(PackedInput)  

dNbr              s             31a                             

dNbrEdited        s            256                              

dEdtMsk           s            256a                             

dLenEdtMsk        s             10i 0                       

dLenEdtVal        s             10i 0                       

dZeroSupr         s              1a                         

dZonedOutput      ds                  qualified             

d Zoned                          1    inz(x'02')           

d Scale                          3u 0                      

d Precision                      3u 0                      

d                               10i 0 inz(0)               

dPackedInput      ds                  qualified            

d Packed                         1    inz(x'03')           

d Scale                          3u 0 inz(%decpos(NbrIn))  

d Precision                      3u 0 inz(%len(NbrIn))     

d                               10i 0 inz(0)               

 /free                                                      

  QUSBPrv = 0;                                               

  monitor;                                                   

  CvtEdtCd(EdtMsk :LenEdtMsk :LenEdtVal :ZeroSupr            

           :EditCode :' ' :Precision :Scale :QUSEC);         

  ZonedOutput.Precision = Precision;                          

  ZonedOutput.Scale = Scale;                                 

  CpyNv(Nbr :ZonedOutput :NbrIn :PackedInput);               

  Edit(NbrEdited :LenEdtVal :Nbr :'*ZONED' :Precision         

       :EdtMsk :LenEdtMsk :ZeroSupr :QUSEC);                  

  return %subst(NbrEdited :1 :LenEdtVal);                     

  on-error;                       

    return '*** Error ***';      

  endmon;                        

 /end-free                       

pEditC            e              

To create the EDITC module, run the following Create RPG Module command.

CRTRPGMOD MODULE(EDITC)

You can create the *SRVPGM EDITS using this command:

CRTSRVPGM SRVPGM(EDITS) MODULE(EDITC) EXPORT(*ALL)

To create the UDF EDITC, run the following Create Function SQL statement (replacing the library name VINING with the name of the library you created the *SRVPGM into).

Create Function EditC (NumericValue Decimal(31,9), 

                       Precision    Integer,       

                       Scale        Integer,       

                       EditCode     VarChar(1))    

    Returns VarChar(256)                           

    Language RPGLE                                 

    Parameter Style General                        

    Not Deterministic                               

    No SQL                                         

    Returns Null on Null Input                     

    Not Fenced                                     

    External Name 'VINING/EDITS(EDITC)'            

The EDITC function defines four input parameters and returns a return value. The first parameter is the numeric value to be edited. This parameter is defined as a packed decimal variable with a precision of 31 digits and a scale of 9 digits. Due to SQL's promotion of data types, the actual data type that you might specify when invoking the UDF could be zoned decimal, packed decimal, or integer. Likewise, an RPG developer could directly access the *SRVPGM function using any of these data types because the parameter is prototyped as const.

The limitation of 31 digits for the precision of the numeric value is due to the requirement that the UDF run on V5R4. Looking at the V6R1 API documentation, found here, you can see that the QECCVTEC was enhanced to support a precision of up to 63 digits, the same as for ILE RPG. The scale of 9 digits was rather arbitrarily chosen by me.

The second parameter is the precision of the numeric value you want to edit. This is the precision of the variable (or database column) you are passing to the UDF. So if the numeric value is defined in the database as decimal 15,5, you would specify 15 for the second argument of the UDF.

The third parameter is the scale of the numeric value you want to edit. Following the earlier example of a decimal 15,5 variable, you would specify 5 for the third argument of the UDF.

The fourth parameter is the edit code you want to use. The edit code can be A-D, J-Q, W, Y-Z, or 1-9. A description of these edit codes can be found here.

The return value of the UDF is defined as a variable-length character string with a maximum length of 256 bytes. After invoking the UDF, this return value will contain the edited numeric value.

Assume that we have a table with column int_nbr defined as an integer field and that rows exist within the table. One row has an int_nbr value of 1 with the other an int_nbr value of -1. Using the EDITC UDF with edit code values of 'B', 'K', 'O', and '2' will result in the following returned values.

Select Statement                        Returned Value for 1   Returned Value for -1

Select editc(int_nbr, 10, 0, 'B')…       1                     1CR

Select editc(int_nbr, 10, 0, 'K')…       1                     1- 

Select editc(int_nbr, 10, 0, 'O')…       1                     -1

Select editc(int_nbr, 10, 0, '2')…       1                     1

Turning to the source of the EDITC function, using the two APIs is quite straightforward.

The EDITC function starts by setting the Bytes provided field of the API error code structure to zero (0). This is done so that any errors are retained in the job log. The function then establishes a monitor group to catch any errors that are encountered while the function is running. If an error is encountered, EDITC returns the character string '*** Error ***' as shown in the following example where EDITC is passed an invalid edit code of '?'.

Select Statement                    Returned Value

Select editc(Amount, 9, 2, '?')…    *** Error ***

This '*** Error ***' return value can be encountered in situations such as an invalid edit code (shown above), a precision argument value outside of the supported range of 1 to 31, or the loss of any digits to the left of the decimal point. Other types of errors, such as a numeric value with greater than 22 significant digits and/or a numeric value with greater than 9 decimal positions, will be detected by SQL prior to the UDF being invoked (and use SQL error-handling support).

Having started the monitor group, EDITC calls the QECCVTEC API to generate an edit mask. The API defines nine parameters: four outputs, four inputs, and the input/output error code parameter. The four output parameters are used by the API to return to you, in this order, the generated edit mask, the length of the returned edit mask, the length that a receiver variable must be in order to return an edited numeric value for the given edit mask, and the zero balance fill character that should be used for correct zero suppression when using the returned edit mask. The four API input parameters, in this order, define the edit code to be used, the fill character to be used for zero suppression, the precision of the numeric value to edit, and the number of decimal positions (scale) for the numeric value to edit.

For demonstration purposes, assume that we have a column 'Amount' that is defined as a packed decimal field (9,2). To edit Amount using edit code 'A', we would call EDITC as in 'Select editc(Amount, 9, 2, 'A')…. The EDITC function, when calling the QECCVTEC API, uses…

1. the variable EdtMsk to receive the generated edit mask (this variable is required by the API to be 256 bytes in length)
2. the variable LenEdtMsk to receive the actual length of the edit mask returned in the EdtMsk parameter
3. the variable LenEdtVal to receive the required length of a receiver variable to receive the edited output of the EdtMsk edit mask when using the QECEDT API
4. the variable ZeroSupr to receive the zero suppression control character to be used with the returned edit mask
5. the variable EditCode, which represents the fourth parameter passed to the EDITC function ('A')
6. the constant of one blank to indicate that suppressed zeroes are to be replaced by blanks (a later example demonstrates using a non-blank suppression character)
7. the variable Precision, which represents the second parameter passed to the EDITC function (9)
8. the variable Scale, which represents the third parameter passed to the EDITC function (2)

The QECCVTEC API then generates the proper edit mask and returns it to us. The program now needs to adjust the input parameter NbrIn from its current definition of packed (31,9) to (9,2), which is the precision and scale to use with the previously created edit mask. To accomplish this, EDITC uses the bound machine interface (MI) instruction Copy Numeric Value (LBCPYNV) documented here for V5R4. The LBCPYNV instruction enables you to copy numeric values of one definition—for instance, packed (31,9)—to another definition—for instance zoned (9.2)—without having to know the type, precision, or scale at compile time. In other words, this is one very powerful eval!

The LBCPYNV instruction defines four parameters. The first parameter is the receiver variable where the instruction returns the copied numeric value. The second parameter is an input structure that defines the desired attributes of the numeric value to be returned in the first parameter. The third parameter is the numeric value to be copied to the first parameter. The fourth parameter is an input structure that defines the attributes of the numeric value found in the third parameter.

Both parameters two and four use the same structure definition, which can be found here. The parameters are a seven-byte data structure where the first byte defines the type of data (x'02' for zoned decimal, x'03' for packed decimal, etc.), the second byte defines (as a binary value) the scale of the data, the third byte defines (as a binary value) the precision of the data, and the last four bytes are reserved with a required value of x'00000000'.

For demonstration purposes, assume that EDITC was called as in 'Select editc(Amount, 9, 2, 'A')…. The EDITC function, when using the LBCPYNV instruction, uses…

1. the variable Nbr to receive the converted numeric value of NbrIn (which reflects the Amount value passed to the UDF). Nbr is defined as a 31-byte character string with 31 bytes reflecting the maximum number of digits supported by the QECCVTEC API for V5R4
2. the variable ZonedOutput to define the desired attributes of the returned Nbr variable. ZonedOutput defines a zoned decimal value with a precision of nine digits and a scale of two digits. These values were previously set by the two preceding instructions and reflect the second and third arguments passed to the EDITC UDF.
3. the variable NbrIn
4. the variable PackedInput  to define the attributes of the NbrIn variable. PackedInput defines a packed decimal value with a precision of 31 digits and a scale of nine digits. These values are set based on the definition of the NbrIn parameter.

EDITC utilizes the RPG built-ins %len and %decpos (rather than hard-coding nine and two, respectively) in order to minimize the changes necessary within the module if you were to upgrade to V6R1 and wanted EDITC to start supporting numeric values up to 63 digits in length. By changing the SQL UDF definition, the allocated size of the Nbr variable, the EDITC prototype, and the EDITC program interface specification to the desired precision and scale, a recompile should be all that's required.

Having converted the input NbrIn value to the necessary type, precision, and scale to use with the previously generated edit mask, it's time to call the QECEDT API to format the numeric value. The QECEDT API defines nine parameters: one output, seven inputs, and the input/output API error code. The first parameter is the receiver variable where the edited numeric value will be returned.

Following this output parameter, the second parameter is the length of the receiver variable, which I have to admit is the one parameter that I tend to use incorrectly when I've been away from the QECEDT API for a while. Most system APIs allow you to pass a length of receiver variable value that can be larger than is actually necessary. That is, an API might only have 100 bytes of data to return, but it's acceptable to provide a receiver variable that is 256 bytes in length. The API will just return the 100 bytes that it can and leave the remainder of the receiver variable untouched. The QECEDT API, on the other hand, requires that the length of the receiver variable value passed as the second parameter be exactly the number of bytes required to hold the edited numeric value. So while the EDITC receiver variable, NbrEdited, is allocated with 256 bytes, the program does not use %size(NbrEdited) to set the value of the second parameter (which is what I usually do with Length of receiver variable parameters and is the mistake I generally make with QECEDT). Rather, EDITC uses LenEdtVal—the value returned in the third parameter of the QECCVTEC API—as the length of the receiver variable. I suspect this behavior—requiring an exact Length of receiver variable value—is due to the QECEDT API being one of the very early system APIs and not implementing a Bytes returned/Bytes available solution. This API has been available since V2R2 after all.

The third parameter is the numeric value to be edited with the fourth and fifth parameters further describing this numeric value. The fourth parameter defines the format of the numeric value: *ZONED for zoned decimal as is the case with EDITC, *PACKED for packed decimal, or *BINARY for integer data. The fifth parameter defines the precision of the numeric value. In the case of the EDITC sample program, this is the Precision provided by the user as the second argument when invoking the UDF.

The next three parameters are values that were previously returned by the QECCVTEC API. These parameters are the edit mask to be used, the length of the edit mask, and the zero balance fill character respectively.

After calling the QECEDT API, the EDITC UDF returns the edited value using %subst to return only the first LenEdtVal bytes of the NbrEdited receiver variable.

Assuming that two values of an Amount column in a table are 1.23 and -1.23 and that your job decimal format is defined as the period, the following results will be returned by the EDITC UDF given the arguments shown.

Select editc(Amount, 9, 2, 'A')

        1.23      

        1.23CR    

Select concat(editc(Amount, 9, 2, 'A'), ' Dollars')

        1.23   Dollars 

        1.23CR Dollars 

Select concat(trim(editc(Amount, 9, 2, 'A')), ' Dollars') 

       1.23 Dollars  

       1.23CR Dollars

To demonstrate other features of EDITC, assume that you have the column MyDate, which is defined as a numeric value (rather than a Date data type). Using edit code 'Y', EDITC can be used to return MyDate formatted as a date.

Select char(editc(MyDate, 8, 0, char('Y')), 10)

      5/18/2011

To use a non-blank zero suppression character within EDITC, you can specify the desired character as the sixth parameter when creating the edit mask with the QECCVTEC API, as shown below (or add it as a parameter to the UDF).

CvtEdtCd(EdtMsk :LenEdtMsk :LenEdtVal :ZeroSupr   

         :EditCode :'*' :Precision :Scale :QUSEC);

Select editc(Amount, 9, 2, 'A')

********1.23 

********1.23CR

To alter the returned decimal format, you can…

CHGJOB DECFMT(J)

Select editc(Amount, 9, 2, 'A')

        1,23     

        1,23CR   

Being an export of the EDITS service program, EDITC can also be accessed without the use of SQL. An RPG program can utilize EDITC directly as demonstrated below.

Result = EditC(Amount :%len(Amount) :%decpos(Amount) :'A');

You should find that the use of the QECCVTEC and QECEDT APIs is very straightforward and that the APIs provide a high degree of flexibility in terms of providing run-time editing of numeric values. As I have in the past encountered a few companies that do not allow the use of MI instructions within application programs, I will point out that EDITC's use of the LBCPYNV instruction can be replaced by a few RPG instructions (without losing any of the flexibility found with the current EDITC). If your company happens to be one of these, let me know; a future article can demonstrate a non-MI based implementation of EDITC.

As usual, if you have any API questions, send them to me at This email address is being protected from spambots. You need JavaScript enabled to view it.. I'll see what I can do about answering your burning questions in future columns.

 as/400, os/400, iseries, system i, i5/os, ibm i, power systems, 6.1, 7.1, V7, V6R1