MainframeKit

-117

SQLSTATE 42802NEGATIVE SQLCODE

The statement gives a different number of values than there are target columns. Usually an INSERT whose column list and VALUES list do not line up.

MOST LIKELY

An INSERT without a column list, run after a column was added to the table.

FIRST CHECK

Count the target columns and the values side by side. Make the counts equal.

TRY IT ▸

MainframeKit assistant

What it means

Every target column needs exactly one value. Db2 counts the columns you named (or all the table's columns, if you named none) and the values you supplied. If the two counts differ, it rejects the statement with -117 and SQLSTATE 42802, and nothing is inserted or changed.

The same check applies to multi-column assignments, such as SET (A, B) = (...) in an UPDATE or a SET statement.

Typical causes

  • An INSERT without a column list, run after a column was added to the table.
  • A column added to the column list but its value forgotten in VALUES (or the other way round).
  • A stray or missing comma that merges or splits two values.
  • A subselect in INSERT ... SELECT returning more or fewer columns than the target list.
  • A multi-column SET in an UPDATE with unequal left and right sides.

How to fix it

  1. Count the target columns and the values side by side. Make the counts equal.
  2. Always name the columns in an INSERT, so new table columns do not break the program.
  3. For INSERT ... SELECT, check the select list against the target column list one by one.
  4. Check the table's current column list in SYSIBM.SYSCOLUMNS if the program has no column list.

Example

Illustrative only; names and values are invented.

INSERT INTO ORDHDR (ORD_NO, CUST_NO, ORD_DATE)
       VALUES (:WS-ORD-NO, :WS-CUST-NO)
-- 3 columns, 2 values: SQLCODE -117, SQLSTATE 42802