NOT FOUND in SQLCODE

When a SELECT or FETCH statement encounters NOT FOUND (or END OF DATA), the database server sets SQLCODE (sqlca.sqlcode) to 100. The following table lists conditions that cause SQL statements to yield NOT FOUND.

Table 1. SQLCODE values that are set when SQL statements do not return any rows

SQL statement where SQLCODE gets the indicated result	Result for ANSI-compliant database	Result for Non-ANSI-compliant database
FETCH statement: the last qualifying row has already been returned (the end of data was reached).	100	100
SELECT statement: no rows match the SELECT criteria.	100	100
DELETE and DELETE...WHERE statement (not part of multistatement PREPARE): no rows match the DELETE criteria.	100	0
INSERT INTO tablename SELECT statement (not part of multistatement PREPARE): no rows match the SELECT criteria.	100	0
SELECT... INTO TEMP statement (not part of multistatement PREPARE): no rows match the SELECT criteria.	100	0
UPDATE...WHERE statement (not part of multistatement PREPARE): no rows match the UPDATE criteria.	100	0

In the following example, the INSERT statement inserts into the hot_items table any stock item that has an order quantity greater than 10,000. If no items have an order quantity that great, the SELECT part of the statement fails to insert any rows. The database server returns 100 in an ANSI-compliant database and 0 if the database is not ANSI compliant.

EXEC SQL insert into hot_items
   select distinct stock.stock_num,
         stock.manu_code,description
      from items, stock
      where stock.stock_num = items.stock_num 
         and stock.manu_code = items.manu_code 
         and quantity > 10000;

For readability, use the constant SQLNOTFOUND for the END OF DATA value of 100. The sqlca.h header file defines the SQLNOTFOUND constant. The following comparison checks for the NOT FOUND and END OF DATA conditions:

if(SQLCODE == SQLNOTFOUND)