Data Types and Null Values › Comparison, Assignment, Arithmetic, and Concatenation Operations

Comparison, Assignment, Arithmetic, and Concatenation Operations

The data type, length, and magnitude of a value determine how CA IDMS handles the value during comparison, assignment, arithmetic, and concatenation operations. The same factors also affect the outcome of these operations.

For example:

• Padding can occur during comparisons of values of unequal length.
• Overflow occurs when the magnitude of a numeric value is greater than the largest magnitude represented by the data type of the construct to which the value is assigned.
• Underflow occurs when the magnitude of an approximate numeric value is smaller than the smallest magnitude represented by the data type of the construct to which the value is assigned.
• Truncation can occur when a binary, graphic, or character value is assigned to a construct that is not big enough to hold the value.
• Rounding can occur as a result of truncation during assignment and arithmetic operations.

Binary Values

Comparison

Binary values can be compared to the following:

• Other binary values. When comparing binary values of different lengths, CA IDMS pads the shorter value with binary zeros on the right to make the lengths equal.
• Date/time values. Provided the binary value has a length of 8.
• Character values. If the binary and character values are of different lengths, CA IDMS pads the shorter value with spaces on the right to make the lengths equal.

Assignment

Binary values can be assigned to the following:

• Binary constructs (for example, a column with a data type of BINARY):
  • If the binary value is shorter than the construct, CA IDMS pads the value with binary zeros on the right to make the value as long as the construct.
  • If the binary value is longer than the construct and the construct is a column (for example, in an INSERT or UPDATE operation), CA IDMS truncates the value on the right if the portion to be truncated contains only binary zeros. If nonzero bits would be truncated, CA IDMS generates an exception and no assignment occurs.
  • If the binary value is longer than the construct and the construct is a host variable (for example, in a FETCH or SELECT operation), CA IDMS truncates the value on the right.
• Date/time constructs (for example, a column with a data type of TIMESTAMP), provided the binary value has a length of 8 and conforms to the internal representation of a valid date/time value.
• Character constructs (for example, a column with a data type of CHARACTER). The rules for assigning a binary value to a character construct are the same as those for assigning a character value to a character construct. If padding is necessary, it is done with blanks instead of binary zeros.

Arithmetic

You cannot use binary values in arithmetic operations.

Concatenation

Binary values can be concatenated with other binary values and with character values. For the purposes of concatenation, binary values are treated as character values, and the result of a concatenation operation is a character value.

Character Values

Comparison

Character values can be compared to the following:

• Other character values. When comparing character values of different lengths, CA IDMS pads the shorter value with blanks on the right to make the lengths equal.
• Date/time values, provided the character value has the format of a date/time value of the same data type as the date/time value used in the comparison.
• Binary values. The binary value is treated as a character value and padded with blanks, if necessary, before the comparison is made.

Assignment

Character values can be assigned to the following:

• Character constructs (for example, a column with a data type of CHARACTER or VARCHAR). If the character value is:
  • Shorter than the construct, and the construct has a data type of CHARACTER, CA IDMS pads the value with blanks on the right to make the value as long as the construct. If the construct has a data type of VARCHAR, no padding takes place.
  • Longer than the construct and the construct is a column (for example, in an INSERT or UPDATE operation), CA IDMS truncates the value on the right if all the characters to be truncated are blanks. If nonblank characters would be truncated, CA IDMS generates an exception and no assignment occurs.
  • Longer than the construct and the construct is a host variable, a local variable, or a parameter (for example, in a FETCH, SELECT, or a SET operation), CA IDMS truncates the value on the right and an SQL warning condition is issued.
• Date/time constructs (for example, a column with a data type of TIMESTAMP), provided the character value has the format of a date/time value of the same data type as the construct to which the value is being assigned.
• Binary constructs (for example, a column with a data type of BINARY). The rules for assigning a character value to a binary construct are identical to those for assigning a binary value to a binary construct. If padding is necessary, it is done with binary zeros instead of blanks.

Arithmetic

You cannot use character values in arithmetic operations.

Concatenation

Character values can be concatenated with other character values.

If one value in the concatenation:

• Has a data type of VARCHAR, the result has a data type of VARCHAR. Otherwise, the result has a data type of CHARACTER.
• Is null, the result is null. Otherwise, the length of the result is the sum of the lengths of the two values that are concatenated. The length of the result cannot exceed 32,760.

A character value and a binary value can be concatenated. The binary value is treated as a character value for the purpose of concatenation.

Date/time Values

Comparison

Date/time values can be compared to:

• Other date/time values of the same data type
• Binary values, provided the binary value has a length of 8
• Character values, provided the character value has the format of a date/time value of the same data type as the date/time value used in the comparison

Assignment

Date/time values can be assigned to the following:

• Date/time constructs of the same data type. For example, a value with a data type of TIMESTAMP can be assigned to a column with a data type of TIMESTAMP.
• Binary constructs with a length of 8.
• Character constructs long enough to contain the date/time value

  If the date/time value is:

  • Shorter than the character construct, CA IDMS pads the value with blanks on the right to make the value as long as the construct
  • Longer than the character construct, CA IDMS generates an exception and does not perform the assignment

Arithmetic

You can use date/time values only in addition and subtraction. Special rules govern date/time arithmetic.

Note: For information about date/time arithmetic, see Date/time Arithmetic.

Concatenation

You can concatenate a date value and a time value using the TIMESTAMP function.

Note: For more information about the TIMESTAMP function, see CA IDMS Scalar Functions.

Graphics Character Values

Comparison

Graphics character values can be compared to other graphics character values. When comparing graphics character values of different lengths, CA IDMS pads the shorter value with double-byte blank characters on the right to make the lengths equal.

Assignment

Graphics character values can be assigned to graphics character constructs (for example, to a column with a data type of GRAPHIC or VARGRAPHIC): If the graphics character value is:

• Shorter than the construct, and the construct has a data type of GRAPHIC, CA IDMS pads the value with double-byte blank characters on the right to make the value as long as the construct. If the construct has a data type of VARGRAPHIC, no padding takes place.
• Longer than the construct and the construct is a column (for example, in an INSERT or UPDATE operation), CA IDMS truncates the value on the right if the portion to be truncated contains only double-byte blank characters. If nonblank characters would be truncated, CA IDMS generates an exception and no truncation occurs.
• Longer than the construct and the construct is a host variable (for example, in a FETCH or SELECT operation), CA IDMS truncates the value on the right.

Arithmetic

You cannot use graphics character values in arithmetic operations.

Concatenation

Graphics character values can be concatenated with other graphics character values.

If one of the values has a data type of VARGRAPHIC, the result has a data type of VARGRAPHIC. Otherwise, the result has a data type of GRAPHIC.

If one value in the concatenation is null, the result is null. Otherwise, the length of the result is the sum of the lengths of the two values that are concatenated. The length of the result cannot exceed 32,760.

Usage

The use of graphics data requires the installation of CA IDMS DBCS.

Numeric Values

Comparison

Numeric values can be compared only to other numeric values.

Assignment

Numeric values can be assigned only to numeric constructs (for example, to a column with a data type of DECIMAL).

When assigning a value of one numeric data type to a construct of a different numeric data type, CA IDMS converts the value to the data type of the construct:

• If the conversion results in overflow or underflow, CA IDMS generates an exception and does not perform the assignment.
• If the conversion results in the loss of digits to the right of the decimal point in an exact numeric value or least significant digits in the mantissa of an approximate numeric value, CA IDMS rounds the value and does not generate an exception.

Arithmetic

You can use values of all numeric data types (both approximate and exact) in arithmetic operations. Additionally, a single arithmetic expression can include values of more than one numeric data type.

Data Type Conversion for Comparison and Arithmetic

When a comparison or arithmetic operation involves two values of different numeric data types, CA IDMS determines which data type has higher precedence. CA IDMS then converts both values to a common data type based on the data type of higher precedence:

Precision of the Result

The precision of the result of comparison or arithmetic operation when a conversion involves a decimal data type is:

• The maximum number of digits to the left of the decimal in the source operands plus the result scale plus 1, if the operation is addition or subtraction (The result scale is the maximum of the source scales)

  For example, the precision of the result of 45673 + 5.398 is 9.

• The sum of the precision of the first value and the second value, if the operation is multiplication or division

  For example, the precision of the result of 45 x 367 is 5.

Examples of Data Type Conversion

For example, to add a NUMERIC value to an INTEGER value, CA IDMS converts both values to DECIMAL. To compare a FLOAT value to a REAL value, CA IDMS converts both values to DOUBLE PRECISION.