如何解决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关键字，或无效的空值。

我已经阅读错误信息这里，我仍然在努力为什么我的声明不会执行。

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版本的Mainframe版本），这样做并不会进行大量的自动类型转换。或者您正在发送 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

$ b另外，由于您使用 MERGE ，您不必在更新中使用参数或 INSERT 部件，可以参考您传入的 USING 表中的值。此外，由于您在 TEST_ID 中匹配，因此您不需要包含在您的更新语句中，因为它不会被更新，无论如何。 ：）

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屋！