在Squirrel客户端执行DB2 SQL遇Unexpected token错误求助

问题场景 #

在Squirrel客户端执行DB2 SQL查询时，首次执行能返回预期结果，但后续执行抛出Unexpected token错误，具体信息如下：

原查询语句 #

SELECT X.*,CONCAT(TVERTRAG.FIRMA,TVERTRAG.POLLFNR) AS POLICY_NUMBER FROM (
 SELECT VERTR#, AINHALT, REPLACE(LEFT(RIGHT(AINHALT, LENGTH(AINHALT) - LOCATE('##', AINHALT, 8)),
         LOCATE('##', CONCAT(RIGHT(AINHALT, LENGTH(AINHALT) 
                                    - LOCATE('##', AINHALT, 8))
                    ,'##')
                  )-1),'#','')
         AS RENEWAL_DATE, LEFT(AINHALT, LENGTH(AINHALT) - (LENGTH(AINHALT)-1)) AS COLLECTION_TYPE FROM TVERTRAG_GEN where ABEZ = 'IE_RENEW'  
  AND (AINHALT <> '' OR (AINHALT <> 'INCLUDED' OR AINHALT <> 'EXCLUDED')) 
  AND VERTR# IN (SELECT VERTR# FROM TVERTRAG where STADIUM = 3 AND ZUSTAND = 'A')) X, TVERTRAG  
 WHERE X.VERTR# = TVERTRAG.VERTR# 
 AND DATE(X.RENEWAL_DATE) = DATE('2023-10-01')

错误信息 #

An unexpected token "," was found following "OM (
 SELECT VERTR#".  Expected tokens may include:  "<join_type_without_spec> JOIN <join_operand>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.66.46 SQL Code: -104, SQL State: 42601

问题原因与修正方案 #

1. 解析歧义与客户端缓存：错误指向子查询后的逗号，是因为隐式连接（逗号分隔表）的写法在Squirrel后续执行中易引发解析歧义，同时客户端执行缓存也可能干扰语句解析，建议改用显式JOIN语法替代隐式连接。
2. 逻辑条件无效：原查询中(AINHALT <> '' OR (AINHALT <> 'INCLUDED' OR AINHALT <> 'EXCLUDED'))逻辑永远为真（任何值无法同时等于两个不同字符串），需调整为有效逻辑，比如排除空值和特定状态值。

修正后的SQL语句 #

SELECT 
    X.*,
    CONCAT(TVERTRAG.FIRMA, TVERTRAG.POLLFNR) AS POLICY_NUMBER 
FROM (
    SELECT 
        VERTR#, 
        AINHALT, 
        REPLACE(
            LEFT(
                RIGHT(AINHALT, LENGTH(AINHALT) - LOCATE('##', AINHALT, 8)),
                LOCATE('##', CONCAT(RIGHT(AINHALT, LENGTH(AINHALT) - LOCATE('##', AINHALT, 8)), '##')) - 1
            ), '#', ''
        ) AS RENEWAL_DATE, 
        LEFT(AINHALT, 1) AS COLLECTION_TYPE  -- 简化原冗余计算：LENGTH(AINHALT)-(LENGTH(AINHALT)-1)=1
    FROM TVERTRAG_GEN 
    WHERE ABEZ = 'IE_RENEW'  
      AND AINHALT <> '' 
      AND AINHALT NOT IN ('INCLUDED', 'EXCLUDED')
      AND VERTR# IN (
          SELECT VERTR# 
          FROM TVERTRAG 
          WHERE STADIUM = 3 AND ZUSTAND = 'A'
      )
) X
INNER JOIN TVERTRAG 
    ON X.VERTR# = TVERTRAG.VERTR# 
WHERE DATE(X.RENEWAL_DATE) = DATE('2023-10-01')

额外建议 #

• 执行前通过Squirrel客户端菜单Session -> Clear SQL Buffer清空语句缓存
• 优先使用显式JOIN语法，避免隐式连接引发的解析问题
• 复杂表达式建议拆分或添加注释，提升可读性与可维护性

内容的提问来源于stack exchange，提问作者Ravi Shankar