SQL iQuery Script Built-in Function


Edit Numeric Value


The EDIT() built-in functions convert a numeric value into an edited numeric results (decimals, dollar signs, etc. similar to Edit Words and Edit Codes in RPG and DDS.


The EDIT built-in function and its variations convert a numeric value into an edited result with decimals, comas and optional currency symbol. All variations of the spelling of this built-in function work the same, the abundance of naming is to accommodate what developers are used to typing for an Edit Code or Edit Word.


  1. Variable or numeric express that will be edited. Usually a Session Variable is specified for this parameter, but a literal or variable can be specified.
  2. Edit Code or Edit Word: Specify any available Edit Code to edit the value. Optionally follow the edit code with a currency symbol such as a $, as in 'J' or 'J$'. Alternatively, and edit word may be specified, such as '$   ,   0,   .  '.
  3. Decimal Length for result. The value specified for parameter 1 is converted t the length specified on this parameter. If no length is specified the length determined by the value of parameter 1 itself.
  4. Decimal Positions. Required when parameter 3 (decimal length) is specified, the decimal positions of the value in parameter 1. If no parameter 3 and 4 are specified the length and decimals are determined by the value specified in parameter 1.
eval &AMT = 1201.69;
eval &AMTDUE = EditC(&Amt, 'J',7,2);
#msg Your amount due = &AMTDUE

The output written to the joblog is:

Your amount due = 1,201.69

select sum( sales ) INTO &YTDSALES
WHERE YEAR( ordDate ) = YEAR( current_date );
IF (&SQLState < '02000');
eval &Sales = Edit(&YTDSALES, 'J$');
#msg YTD Sales is &Sales

The output written tot he joblog is:

YTD Sales is $1,000,074.38