SQLCODE -811

What SQLCODE -811 means

• -811 means a SELECT INTO returned more than one row.
• A singleton SELECT INTO must return exactly one row.
• Zero rows gives +100. More than one row gives -811.

Why -811 happens

• The WHERE clause is not restrictive enough.
• Duplicate data exists in the table.
• A join multiplies rows unexpectedly because of a missing or wrong join condition.

Fixing -811

• Add conditions to the WHERE clause so exactly one row qualifies.
• If multiple rows are legitimate, use a cursor instead of SELECT INTO.
• Use an aggregate such as MAX or MIN when any one row will do - but only if that is correct for the business rule.
• Example:
  
  * Fails with -811 when several rows qualify: EXEC SQL SELECT EMPNAME INTO :WS-EMPNAME FROM EMP WHERE DEPTNO = :WS-DEPTNO END-EXEC * Correct approach when many rows can qualify - use a cursor: EXEC SQL DECLARE C2 CURSOR FOR SELECT EMPNAME FROM EMP WHERE DEPTNO = :WS-DEPTNO END-EXEC EXEC SQL OPEN C2 END-EXEC EXEC SQL FETCH C2 INTO :WS-EMPNAME END-EXEC