# SQLCODE -117: Number of values doesn't match columns

Source: https://wndsoftware.com/mainframe-errors/sqlcode-117

### SQLCODE -117: Number of values doesn't match columns

SQLCODE -117 (SQLSTATE 42802) means the number of values in an INSERT, UPDATE or SET doesn't match the number of columns they are assigned to. Db2 rejects the statement and changes nothing.


#### Other names for this code

Also written as `SQL -117`, `SQLCODE=-117`, `SQLCODE = -117`, `db2 -117`, `sqlstate 42802`, `SQL0117N`, `number of values assigned is not the same`.


### What it means

Every value in an assignment needs exactly one target column. In an INSERT, the values in the VALUES clause, or the columns of the SELECT in an INSERT ... SELECT, are matched one for one with the column list. If there is no column list, Db2 uses every column of the table, in the order they were defined. In an UPDATE that sets several columns at once, such as `SET (A, B) = (X, Y)`, the two sides must also be the same length.

When the counts differ, Db2 returns -117. For static SQL, Db2 usually finds it at bind time, and the bind fails. For dynamic SQL it comes back on PREPARE or EXECUTE IMMEDIATE.

The most common story is an INSERT with no column list that worked for years. Then someone adds a column to the table. The statement now implies one more column than it supplies, and the next bind or rebind of the package fails with -117.


### Common causes

- An INSERT without a column list, written before a column was added to the table.
- A value left out of a long VALUES list, or one value too many, often after a copy and paste.
- An INSERT ... SELECT where the SELECT returns a different number of columns from the target list, for example SELECT * from a table that has changed.
- A multi-column SET in an UPDATE or a SET statement with unequal lists on the two sides.
- The insert part of a MERGE with a column list and values list of different lengths.


### How to find the problem

- Count the target columns and the values in the failing statement. The bind output names the statement number in the DBRM, and the precompile listing maps that number to a line.
- If the INSERT has no column list, count the columns the table really has with `SELECT COUNT(*) FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = 'PAYPROD' AND TBNAME = 'EMP_HIST'`.
- Compare the DCLGEN copybook the program uses with the current table. A stale DCLGEN is a strong clue that the table changed.
- For INSERT ... SELECT, list the SELECT's columns explicitly and count them against the target.


### How to fix it

- Always name the columns in an INSERT. It stops a table change from breaking the program and makes the statement easier to read.
- Add the missing value, or remove the extra one, so each column gets exactly one value.
- Use the DEFAULT keyword in the VALUES list for a column that should take its default value.
- Regenerate the DCLGEN after a table change, update the program, then precompile and bind again.
- Replace SELECT * in INSERT ... SELECT with a named column list.


### What it looks like


```text
DSNT408I SQLCODE = -117, ERROR:  THE NUMBER OF VALUES ASSIGNED IS NOT THE SAME AS THE NUMBER
         OF SPECIFIED OR IMPLIED COLUMNS
DSNT418I SQLSTATE   = 42802 SQLSTATE RETURN CODE
```


### IBM documentation

[IBM documentation: -117](https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=codes-117). Use the documentation for your installed release.

[Report a correction to WND Software](https://wndsoftware.com/contact#message).

Technical reviewer: [Albert Joseph](https://wndsoftware.com/authors/albert-joseph), WND Software.

Last reviewed 2026-10-18 by the WND Software team. Written in our own words. For the full reference, check IBM’s Db2 for z/OS documentation for your release.


### Related codes

[SQLCODE -206 Column name not valid Read →](https://wndsoftware.com/mainframe-errors/sqlcode-206)
[SQLCODE -205 Column not in the named table Read →](https://wndsoftware.com/mainframe-errors/sqlcode-205)
[SQLCODE -407 Null value in a NOT NULL column Read →](https://wndsoftware.com/mainframe-errors/sqlcode-407)
[SQLCODE -803 Duplicate key Read →](https://wndsoftware.com/mainframe-errors/sqlcode-803)

### Questions


#### Why did -117 appear when nobody changed the program?

The table probably changed. An INSERT with no column list depends on the table's current columns, so adding a column breaks it at the next bind or rebind.


#### Do I have to give a value for every column?

Not if you name the columns. Columns you leave out get their default value, or null if they allow it. Without a column list you must supply every column.


#### Is it the same on Db2 for LUW?

Yes. SQL0117N has SQLSTATE 42802 and the same meaning.

[Db2 SQLCODEs](https://wndsoftware.com/mainframe-errors/db2-sqlcodes) · [SQLCODE -104](https://wndsoftware.com/mainframe-errors/sqlcode-104) · [SQLCODE -119](https://wndsoftware.com/mainframe-errors/sqlcode-119)

[← All error codes](https://wndsoftware.com/mainframe-errors/)