Basic Language Elements › Basic Operations (Assignment and Comparison) › Numeric Assignments

Numeric Assignments

The following table lists the conversion rules which apply when assigning a number of one data type to another data type.

Conversion implies that the value is represented in a form compatible for the assignment operation.

To determine the rule which applies when assigning a number of one data type to another data type, locate the original data type in the left column. Next, locate the target data type in the vertical columns to the right.

The rule number specified at the intersection of the two columns is the rule which applies during the assignment operation. See the list of numbered rules following the table for an explanation of the conversion required during the assignment operation.

The following list contains explanations of the numbered rules referenced in the previous table.

1. Decimal or integer to floating-point

   Floating-point numbers are approximations of real numbers. Thus, decimal and integer numbers may not be identical to the original number when assignment is to a floating-point column or variable.

2. Floating-point to integer

   The fractional part of the number is lost on this assignment.

3. Decimal to decimal

   When a decimal number is assigned to a decimal column or variable, the number is converted, if necessary, to the precision and scale of the target.

   In the whole part of the number, the necessary number of leading zeros is appended or eliminated.

   In the fractional part of the number, the necessary number of leading zeros is appended, or the necessary number of digits exceeding the scale is eliminated.

4. Integer to decimal

   When an integer is assigned to a decimal column or variable, the number is converted to the DECIMAL data type first. Next, it is converted to the precision and scale of the target. For example if X'0005' is placed in a DECIMAL with precision equal to 3 and scale equal to 2, it appears as pack decimal '500C'.

   The precision of a small integer, when converted to DECIMAL, is 5, and the scale is 0.

   The precision of a large integer, when converted to DECIMAL, is 11, and the scale is 0.

5. Floating-point to decimal

   When a floating-point number is assigned to DECIMAL or NUMERIC, the number is first converted to a temporary packed decimal number of precision 31. If necessary, the number is then truncated to the precision and scale of the target.

   In conversion, the number is rounded (using floating-point arithmetic) to a precision of 31 decimal digits. If the representation requires more than 31 digits to the left of the decimal point, an error is reported. Otherwise, the scale is given the largest possible value that allows the whole part of the number to be represented without loss of significance.

6. Decimal to integer

   The fractional part of the decimal number is truncated.

   The maximum range of values which can be represented for each data type is:

1. DATE, TIME, and TIMESTAMP

   See "Assignment for Dates, Times, and Timestamps" on Assignment for Dates, Times, and Timestamps.

2. VARCHAR to CHAR

   If the varying-length string value is shorter than the length attribute of the CHAR column or variable, the string is padded on the right with blanks until it is the required length.

   If the varying-length string value is longer than the length attribute of a CHAR column, an error occurs.

   If the varying-length string value is longer than the length attribute of a CHAR variable, the string is truncated on the right by the required number of characters (a 'W' is assigned to SQLWARN1 of the SQLCA if this occurs).

3. CHAR to VARCHAR

   If the length attribute of the CHAR column or variable is less than the maximum length of the VARCHAR column or variable, the CHAR string is copied and the length is set to the length of the CHAR string.

   If the length attribute of the CHAR column or variable is longer than the maximum length of the VARCHAR target, an error occurs when the target is a column, or truncation occurs when the target is a variable.

4. MIXED Data

   When a GRAPHIC value is assigned to a larger-precision target, the value is padded with DBCS blanks (X'4040'). When MIXED data is enabled via the CXXMAINT option, SQL does special processing as follows:

   • When a MIXED string is moved to a smaller-precision MIXED host variable, SQL ensures that the truncation results in a well-formed string. If there is an unpaired Shift-Out in the result:
     • If the last byte in the truncated string is the first byte of a DBCS character, that last byte is overlayed with a Shift-In.
     • If the last byte in the truncated string is the second byte of a DBCS character, the last DBCS character is replaced by a Shift-In and an SBCS blank.
     • If the last byte of the truncated string is a Shift-Out, it is overlayed with an SBCS blank.
     • If the next-to-last byte of the truncated string is a Shift-Out, it and the last byte are overlayed with SBCS blanks.
   • SQL issues an error if there are unpaired Shift codes in a string when assigning it to a column or host variable.