Data retrieval errors

 

Use this information to understand how SQL handles errors that occur when retrieving data.

If SQL finds that a retrieved character or graphic column is too long to be placed in a host variable, SQL does the following:

• Truncates the data while assigning the value to the host variable.

• Sets SQLWARN0 and SQLWARN1 in the SQLCA to the value 'W' or sets RETURNED_SQLSTATE to '01004' in the SQL diagnostics area.

• Sets the indicator variable, if provided, to the length of the value before truncation.

If SQL finds a data mapping error while running a statement, one of two things occurs:

• If the error occurs on an expression in the SELECT list and an indicator variable is provided for the expression in error:

  • SQL returns a -2 for the indicator variable corresponding to the expression in error.

  • SQL returns all valid data for that row.

  • SQL returns a positive SQLCODE.

• If an indicator variable is not provided, SQL returns the corresponding negative SQLCODE.

Data mapping errors include:

• +138 - Argument of the substringing function is not valid.

• +180 - Syntax for a string representation of a date, time, or timestamp is not valid.

• +181 - String representation of a date, time, or timestamp is not a valid value.

• +183 - Invalid result from a date/time expression. The resulting date or timestamp is not within the valid range of dates or timestamps.

• +191 - MIXED data is not properly formed.

• +304 - Numeric conversion error (for example, overflow, underflow, or division by zero).

• +331 - Characters cannot be converted.

• +420 - Character in the CAST argument is not valid.

• +802 - Data conversion or data mapping error.

For data mapping errors, the SQLCA reports only the last error detected. The indicator variable corresponding to each result column having an error is set to -2.

For data mapping errors on a multi-row FETCH, each mapping error reported as a warning SQLSTATE will have a separate condition area in the SQL diagnostics area. Note that SQL stops on the first error, so only one mapping error that is reported as an error SQLSTATE will be returned in the SQL diagnostics area.

For all other SQL statements, only the last warning SQLSTATE will be reported in the SQL diagnostics area.

If the full-select contains DISTINCT in the select list and a column in the select list contains numeric data that is not valid, the data is considered equal to a null value if the query is completed as a sort. If an existing index is used, the data is not considered equal to a null.

The impact of data mapping errors on the ORDER BY clause depends on the situation:

• If the data mapping error occurs while data is being assigned to a host variable in a SELECT INTO or FETCH statement, and that same expression is used in the ORDER BY clause, the result record is ordered based on the value of the expression. It is not ordered as if it were a null (higher than all other values). This is because the expression was evaluated before the assignment to the host variable is attempted.

• If the data mapping error occurs while an expression in the select-list is being evaluated and the same expression is used in the ORDER BY clause, the result column is normally ordered as if it were a null value (higher than all other values). If the ORDER BY clause is implemented by using a sort, the result column is ordered as if it were a null value. If the ORDER BY clause is implemented by using an existing index, in the following cases, the result column is ordered based on the actual value of the expression in the index:

  • The expression is a date column with a date format of *MDY, *DMY, *YMD, or *JUL, and a date conversion error occurs because the date is not within the valid range for dates.

  • The expression is a character column and a character cannot be converted.

  • The expression is a decimal column and a numeric value that is not valid is detected.

 

Parent topic:

Retrieving data using the SELECT statement