Conversion Error Handling

Conversion results can produce errors for divide-by-zero, arithmetic overflow, scale, and domain.

Arithmetic Overflow and Divide-by-Zero Errors

Divide-by-zero errors occur when SAP ASE tries to divide a numeric value by zero. Arithmetic overflow errors occur when the new type has too few decimal places to accommodate the results.

This happens during:
  • Explicit or implicit conversions to exact types with a lower precision or scale

  • Explicit or implicit conversions of data that falls outside the acceptable range for a money or date/time type

  • Conversions of hexadecimal strings requiring more than 4 bytes of storage using hextoint

Both arithmetic overflow and divide-by-zero errors are considered serious, whether they occur during an implicit or explicit conversion. Use the arithabort arith_overflow option to specify how SAP ASE handles these errors. The default setting, arithabort arith_overflow on, rolls back the entire transaction in which the error occurs. If the error occurs in a batch that does not contain a transaction, arithabort arith_overflow on does not roll back earlier commands in the batch, and SAP ASE does not execute statements that follow the error-generating statement in the batch. If you set arithabort arith_overflow off, SAP ASE aborts the statement that causes the error, but continues to process other statements in the transaction or batch.You can use the @@error global variable to check statement results.

Use the arithignore arith_overflow option to determine whether SAP ASE displays a message after these errors. The default setting, off, displays a warning message when a divide-by-zero error or a loss of precision occurs. Setting arithignore arith_overflow on suppresses warning messages after these errors. You can omit optional arith_overflow keyword without any effect.

Scale Errors

When an explicit conversion results in a loss of scale, the results are truncated without warning.

For example, when you explicitly convert a float, numeric, or decimal type to an integer, SAP ASE assumes you want the result to be an integer and truncates all numbers to the right of the decimal point.

During implicit conversions to numeric or decimal types, loss of scale generates a scale error. Use the arithabort numeric_truncation option to determine how serious such an error is considered. The default setting, arithabort numeric_truncation on, aborts the statement that causes the error, but continues to process other statements in the transaction or batch. If you set arithabort numeric_truncation off, SAP ASE truncates the query results and continues processing.

Note: For entry-level ANSI SQL compliance, set:
  • arithabort arith_overflow off

  • arithabort numeric_truncation on

  • arithignore off

Domain Errors

The convert function generates a domain error when the function’s argument falls outside the range over which the function is defined. This rarely happens.