如何解决 SQL0418N 错误 [英] How to resolve SQL0418N Error

问题描述

我正在使用下面的语句将一些数据更新/插入到表中，如果我在没有参数的情况下运行它，那很好.但是，一旦我尝试使用它抛出的参数执行它:

I'm using the statement below to update/insert some data to a table and, if I run it without parameters, it's fine. However, as soon as I try to execute it using parameters it throws:

SQL0418N - 语句包含使用无类型参数标记、DEFAULT 关键字或无效的空值.

我已阅读错误信息 here，但我仍在为为什么我的语句无法执行而苦苦挣扎.

I've read the error information here, but I'm still struggling with why my statement won't execute.

--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;

提前感谢您的帮助！

推荐答案

基本上，DB2 不知道您在这些参数上发送的数据类型.我猜您使用的是旧版本的 DB2(Linux/Unix/Windows 上小于 9.7，或者使用旧于 10.1 的大型机版本)，它不会进行大量的自动"类型转换.或者您正在发送 NULL 值(仍然必须输入"，听起来很奇怪).

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

此外，由于您使用的是 MERGE，因此您不必在 UPDATE 或 INSERT 部分中使用参数，您可以引用您传入的 USING 表中的值.此外，由于您在 TEST_ID 上进行匹配，因此您无需将其包含在您的 中UPDATE 语句，因为无论如何它不会被更新.

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.

这篇关于如何解决 SQL0418N 错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！