SQL0418N when inserting with select from the same table.

rydenius · #1 (**permalink**) 10-12-04, 04:02

I have the following table (simplified):

CREATE TABLE aaa (
bbb INTEGER NOT NULL,
ccc INTEGER NOT NULL,
ddd INTEGER NOT NULL
);

There is a uniqe index on bbb,ccc. Try to execute the following PreparedStatement via JDBC:

INSERT INTO aaa (bbb, ccc, ddd) SELECT ?, ccc, ddd FROM aaa WHERE bbb=?

(Intention: copy all rows with a specific bbb value to new rows with another bbb value)

Get the following exception while executing in DB2:

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/6000] SQL0418N A statement contains a use of a parameter marker that is not valid. SQLSTATE=42610
at COM.ibm.db2.jdbc.net.SQLExceptionGenerator.throw_S QLException(SQLExceptionGenerator.java:238)
at COM.ibm.db2.jdbc.net.SQLExceptionGenerator.check_r eturn_code(SQLExceptionGenerator.java:402)
at COM.ibm.db2.jdbc.net.SQLExceptionGenerator.check_r eturn_code(SQLExceptionGenerator.java:380)
at COM.ibm.db2.jdbc.net.DB2PreparedStatement.executeU pdate(DB2PreparedStatement.java:717)
at org.jboss.resource.adapter.jdbc.WrappedPreparedSta tement.executeUpdate(WrappedPreparedStatement.java :335)
...

Does anybody know how I can adjust my SQL statement (hopefully in a db vendor neutral way)?

rydenius · #2 (**permalink**) 10-12-04, 04:57

Found solution:

INSERT INTO aaa (bbb, ccc, ddd) SELECT CAST (? AS INTEGER), ccc, ddd FROM aaa WHERE bbb=?

Obviously DB2 requires constants to be explicitly type casted.