How to resolve SQL0418N Error

by

Basically, DB2 doesn’t know what data types you’re sending in on those parameters. I’m guessing you’re 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 doesn’t do a whole lot of “automatic” type conversion. Or you’re sending in NULL values (which still have to be “typed”, strange as it sounds).

You can fix the problem by creating your parameter markers as typed parameters (I’m assuming data types here, use what would be 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

Additionally, since you’re using the MERGE, you don’t have to use parameters in the UPDATE or INSERT parts, you can refer to the values in the USING table you passed in. Also, since you’re matching on TEST_ID, you don’t need to include that in your UPDATE statement, since it wouldn’t be updated, anyway.

More Related Contents:

  1. Does DB2 have an “insert or update” statement?
  2. Solutions for INSERT OR UPDATE on SQL Server
  3. SQLite – UPSERT *not* INSERT or REPLACE
  4. How do I update if exists, insert if not (AKA “upsert” or “merge”) in MySQL?
  5. How to use RETURNING with ON CONFLICT in PostgreSQL?
  6. Is SELECT or INSERT in a function prone to race conditions?
  7. Oracle: how to UPSERT (update or insert into a table?)
  8. Upserting in MS-access
  9. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  10. DB2 Comma Separated Output by Groups
  11. how to transform comma separated column into multiples rows in db2
  12. INNER JOIN in UPDATE sql for DB2
  13. Pivoting in DB2
  14. Insert Update stored proc on SQL Server
  15. Regular Expressions in DB2 SQL
  16. Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3
  17. DB2 Query to retrieve all table names for a given schema
  18. Return rows from INSERT with ON CONFLICT without needing to update
  19. How to include excluded rows in RETURNING from INSERT … ON CONFLICT
  20. UPSERT *not* INSERT or REPLACE
  21. How to check db2 version
  22. What is the difference between Left, Right, Outer and Inner Joins? [duplicate]
  23. Fastest way to remove non-numeric characters from a VARCHAR in SQL Server
  24. IF EXISTS condition not working with PLSQL
  25. unresolved reference to object [INFORMATION_SCHEMA].[TABLES]
  26. Use SELECT inside an UPDATE query
  27. SQL update trigger only when column is modified
  28. MSAccess – query to return result set of earliest rows with a unique combination of 2 columns
  29. Historical / auditable database
  30. How to automatically generate unique id in SQL like UID12345678?

Leave a Comment