The Expression Handler has three types of variables for numeric literals used in arithmetic operations: integer, floating point, and decimal. Input values from the DBMS can come from columns that have more inclusive types, such as short, integer, long, float, double, and decimal.
The Expression Handler has a decimal type for representing fixed point numbers, numbers with precision and scale (that is, having a decimal point), that can participate in arithmetic calculations (add, subtract, multiply, and divide) and produce an exact result.
To represent decimal type in the grammar, the Expression Handler uses a DECIMAL type modeled on SQL:
<decimal type> ::= DECIMAL
| DECIMAL ( <precision> )
| DECIMAL ( <precision> , <scale> )The following synonyms for DECIMAL are allowed: “DEC”, “decimal”, and “dec”. The default is DECIMAL(9,0) if DECIMAL is coded with no explicit precision and scale. The SQL standard states that scale is 0 and precision is implementation-defined in this case.
A DECIMAL type can be specified in any of the above forms along with the other existing types (BOOLEAN, CHARACTER, DOUBLE, and INTEGER) in the following places:
-
Definition of a user procedure input parameter
-
RETURNS clause, which indicates the datatype of the return value
-
Declaration of a variable in a complex expression, either unnamed or named (user procedure).
For example:
procedure pay_check ( decimal(9,2) salary, decimal(9,2) bonus )
returns decimal(9,2);
declare decimal(9,2) total = 0.0;
declare decimal(9,2) commission = 10.1;
total = salary + bonus + salary*commission;
return total;
end;Numeric values with a functional component include 2 types:
-
Floating point literals
-
with exponent indicator (“E”)
-
with float (“F”, “f”)
-
with double (“D”, “d”) suffix
-
A fixed point literal defined as a number with a decimal point; for example, 2.0, 0.1, or 2.1.
They are processed as follows:
-
Floating point literals as type DOUBLE
-
Fix point literals as type DECIMAL
-
Use the following function to lop off the fractional portion of a decimal number with no rounding; that is, to convert the value 123.66 to 123:
“tointeger(123.66);”-
Use the following function to add a decimal point to a decimal(5) number, and convert it to a decimal(3,2), with no rounding:
The method “todecimal(12367)/100.0;” transposes the input value of “12367” to an output value of “123.67”
-
If you are working with older applications, use the following function to convert a decimal(7) number that contains a date in the form CYYMMDD, into a date string in the standard format, YYYY-MM-DD:
procedure CYYMMDD(decimal(7) input_dec_date) returns char;
/* Inputs a date in special i5 format of CYYMMDD and */
/* returns a date in the standard Java format of yyyy-mm-dd”“yyyy-mm-dd” */
declare int input_int_date = tointeger(input_dec_date);
declare char input_date;
declare char def_date = '1901-01-01';
declare char century;
declare char years;
declare char months;
declare char days;
declare char year;
declare char yyyymmdd;
/* negative numbers are invalid */
if (input_int_date <= 0)
return def_date;
/* also eliminate numbers with too many digits */
if (length(tostring(input_int_date)) > 7)
return def_date;
/* convert the number to 0-padded string in form CYYMMDD */
input_date=copyjustify(tostring(input_int_date),'R','R','0',7);
/* split into parts */
century = substring(input_date, 1, 1);
years = substring(input_date, 2, 2);
months = substring(input_date, 4, 2);
days = substring(input_date, 6, 2);
/* check for invalid months in a year */
if ( tonumber(months) gt 12 or tonumber(months) eq 0)
return def_date;
/* check for invalid days in a month */
if ( tonumber(days) gt 31 or tonumber(days) eq 0)
return def_date;
/* interpret century in AS/400 standard way */
switch(tonumber(century))
{
case 0:
year = '19';
break;
case 1:
year = '20';
break;
case 2:
year = '21';
break;
case 3:
year = '22';
break;
case 4:
year = '23';
break;
case 5:
year = '24';
break;
case 6:
year = '25';
break;
case 7:
year = '26';
break;
case 8:
year = '27';
break;
case 9:
year = '28';
break;
default:
return def_date;
}
year = concat(year, years);
yyyymmdd = concat(year, months, days);
return dateformat ( yyyymmdd , 'yyyy-MM-dd' , 'yyyyMMdd');end;