How to resolve error SQL0418N

Question

I use the instruction below to update / insert some data into the table, and if I run it without parameters, this is normal. However, as soon as I try to execute it using parameters, it throws:

SQL0418N - The statement contains the use of an optional parameter marker, the DEFAULT keyword, or an invalid null value .

I read the error information here , but I'm still afraid why my statement will not be executed.

--This statement works MERGE Into AB.Testing_Table A USING (VALUES('TEST', 'P')) B(TEST_ID, "ACTION") ON (A.TEST_ID = B.TEST_ID) WHEN NOT MATCHED THEN INSERT (TEST_ID, "ACTION") VALUES ('TEST', 'P') WHEN MATCHED THEN UPDATE SET TEST_ID = 'TEST' ,"ACTION" = 'P'; --This statement fails with error SQL0418N MERGE Into AB.Testing_Table A USING (VALUES(@TEST, @ACTION)) B(TEST_ID, "ACTION") ON (A.TEST_ID = B.TEST_ID) WHEN NOT MATCHED THEN INSERT (TEST_ID, "ACTION") VALUES (@TEST, @ACTION) WHEN MATCHED THEN UPDATE SET TEST_ID = @Test ,"ACTION" = @Action; 

Thanks in advance for your help!

Answer 1

Basically, DB2 does not know what data types you send by these parameters. I assume that you are either on an older version of DB2 (less than 9.7 on Linux / Unix / Windows, or on a mainframe version older than 10.1), which does not do much “automatic” type conversion. Or you are sending NULL values ​​(which should still be "printed", oddly enough).

You can fix this problem by creating parameter markers as typed parameters (here I am assuming data types, use what is appropriate):

 MERGE INTO AB.TESTING_TABLE A USING (VALUES ( CAST(@TEST AS CHAR(4)) ,CAST(@ACTION AS CHAR(1)) )) B(TEST_ID, "ACTION") ON (A.TEST_ID = B.TEST_ID) WHEN NOT MATCHED THEN INSERT (TEST_ID, "ACTION") VALUES (B.TEST_ID, B.ACTION) WHEN MATCHED THEN UPDATE SET "ACTION" = B.ACTION 

In addition, since you are using MERGE , you do not need to use the parameters in the UPDATE or INSERT parts, you can refer to the values ​​in the USING table to which you passed. In addition, since you agree to TEST_ID , you do not need to include this in your UPDATE , as it will not be updated. Anyway.