What it means
SELECT ... INTO can only return one row into host variables. A subquery used with =, <, > and similar must return one value. When more than one qualifies, Db2 returns -811 with SQLSTATE 21000. Do not rely on the host variables after a -811.
This code has no useful SQLERRMC tokens naming an object. You identify the statement from the program: its error routine should log the statement number or paragraph name.
Typical causes
- The WHERE clause does not cover the full unique key.
- Data now holds duplicates the design never expected, for example after a conversion or a new product type.
- History or effective-dated rows where the program forgot the date condition.
- A subquery against a child table that now has several rows per parent.
- Programs using -811 deliberately as an "exists" test, which costs more than a proper check.
How to fix it
- Find the failing statement from the program log and listing.
- Re-run the same SELECT in a query tool with the logged key values, using COUNT(*).
- Look at the extra rows. Are they valid business data or bad data?
- Compare the predicate with the table's unique key columns.
- Ask when and how the extra rows arrived.
- If the extra rows are wrong, the data owner corrects them and the job is rerun. If they are valid, change the SQL: add the missing predicate, use a cursor and process all rows, or pick one row deliberately with ORDER BY ... FETCH FIRST 1 ROW ONLY. For existence checks use EXISTS or FETCH FIRST 1 ROW ONLY instead of relying on -811.
Example
Illustrative only; names and values are invented.
DSNT408I SQLCODE = -811, ERROR: THE RESULT OF AN EMBEDDED SELECT
STATEMENT OR A SUBSELECT IN THE SET CLAUSE OF AN UPDATE
STATEMENT IS A TABLE OF MORE THAN ONE ROW, OR THE RESULT OF
A SUBQUERY OF A BASIC PREDICATE IS MORE THAN ONE VALUE
DSNT418I SQLSTATE = 21000 SQLSTATE RETURN CODE