Although the database server automatically handles conversion between data types, several things still can go wrong with a SELECT statement. In SQL programming, as in any kind of programming, you must anticipate errors and provide for them at every point.
One common event is that no rows satisfy a query. This event is signalled by an SQLSTATE code of 02000 and by a code of 100 in SQLCODE after a SELECT statement. This code indicates an error or a normal event, depending entirely on your application. If you are sure a row or rows should satisfy the query (for example, if you are reading a row using a key value that you just read from a row of another table), then the end-of-data code represents a serious failure in the logic of the program. On the other hand, if you select a row based on a key that a user supplies or some other source supplies that is less reliable than a program, a lack of data can be a normal event.
If your database is not ANSI compliant, the end-of-data return code, 100, is set in SQLCODE following SELECT statements only. In addition, the SQLSTATE value is set to 02000. (Other statements, such as INSERT, UPDATE, and DELETE, set the third element of SQLERRD to show how many rows they affected; Modifying Data Through SQL Programs covers this topic.)
Errors that set SQLCODE to a negative value or SQLSTATE to a value that begins with anything other than 00, 01, or 02 are usually serious. Programs that you have developed and that are in production should rarely report these errors. Nevertheless, it is difficult to anticipate every problematic situation, so your program must be able to deal with these errors.
For example, a query can return error -206, which means that a table specified in the query is not in the database. This condition occurs if someone dropped the table after the program was written, or if the program opened the wrong database through some error of logic or mistake in input.
A SELECT statement that uses an aggregate function such as SUM, MIN, or AVG always succeeds in returning at least one row of data, even when no rows satisfy the WHERE clause. An aggregate value based on an empty set of rows is null, but it exists nonetheless.
However, an aggregate value is also null if it is based on one or more rows that all contain null values. If you must be able to detect the difference between an aggregate value that is based on no rows and one that is based on some rows that are all null, you must include a COUNT function in the statement and an indicator variable on the aggregate value. You can then work out the following cases.
| Count Value | Indicator | Case |
|---|---|---|
| 0 | -1 | Zero rows selected |
| >0 | -1 | Some rows selected; all were null |
| >0 | 0 | Some non-null rows selected |
You can handle these inevitable errors in many ways. In some applications, more lines of code are used to handle errors than to execute functionality. In the examples in this section, however, one of the simplest solutions, the default value, should work, as the following example shows:
avg_price = 0; /* set default for errors */
EXEC SQL SELECT avg (total_price)
INTO :avg_price:null_flag
FROM items;
if (null_flag < 0) /* probably no rows */
avg_price = 0; /* set default for 0 rows */
The previous example deals with the following considerations: