Java SQLException SQL0901 和 AS400 MCH1210 错误

Question

我正在通过 Java 应用程序运行一个相当大的查询(~83k 结果)，该查询产生以下错误:

java.sql.SQLException: [SQL0901] SQL system error. Cause . . . . . : An SQL system er ror has occurred. The current SQL statement cannot be completed successfully. The error will not prevent other SQL statements from being processed. Previous messages may indicate that there is a problem with the SQL statement and SQL did not correctly diagnose the error. The previous message identifier was MCH1210. Internal error type 3002 has occurred. If precompiling, processing will not continue beyond this statement. Recovery: See the previous messages to determine if there is a problem with the SQL statement. To view the messages, use the DSPJOBLOG command if running interactively, or the WRKJOB command to view the output of a precompile. An application program receiving this return code may attempt further SQL statements. Correct any errors and try the request again.

来自this页面，似乎错误 SQL0901 意味着我可能有驱动程序问题。

深入研究 MCH1210 错误，得到 this StackOverflow question所以我想也许我也会遇到同样的问题，但还是有一些差异。

当我尝试在 System iNavigator 中运行查询时，它工作得很好。当它在我的应用程序中运行时，我收到上述错误。

这是我的查询:

SELECT
    DWVNDN, DWINVN, DWDTEI, DWRTPE, DWINAM, DWCHKN, DWJCDE
FROM HSDATA4.DWRECAP
WHERE DWDTEI > 20180901 AND (DWRTPE = 2 OR DWRTPE = 3) AND DWINVN IN 
    (SELECT DW.DWINVN 
     FROM HSDATA4.DWRECAP DW
         INNER JOIN HSDATA4.FXVNDCP FX ON DW.DWVNDN = FX.FXVNDN 
     WHERE DW.DWRTPE = 1 AND DW.DWDTEI BETWEEN 20180904 AND 20190904)
ORDER BY DWDTEI ASC, DWVNDN ASC;

我应该注意，当将内部查询从 SELECT ... 更改为 SELECT COUNT(*) 以获取返回的记录计数时，返回值为83314，因此主查询使用了超过 83k 个参数。

我本来打算忽略它，因为我正在得到结果，但似乎有些结果最终被切断，所以我不知道如何继续。

此外，如果您讨厌数据库中的列名...我也是。这不是我的选择...

Answer 1

内连接没有为您提供所需的一切吗？

SELECT
   DW.DWVNDN, DW.DWINVN, DW.DWDTEI, DW.DWRTPE, DW.DWINAM, DW.DWCHKN, DW.DWJCDE
FROM HSDATA4.DWRECAP DW
   INNER JOIN HSDATA4.FXVNDCP FX ON DW.DWVNDN = FX.FXVNDN 
WHERE DW.DWRTPE = 1 AND DW.DWDTEI BETWEEN 20180904 AND 20190904
   AND DW.DWDTEI > 20180901 AND DW.DWRTPE IN (2,3)
ORDER BY DW.DWDTEI ASC, DW.DWVNDN ASC;