在子查询中添加INNER JOIN导致WHERE子句语法错误

问题场景 #

原有SQL可正常运行，在QTD_COMPRADA对应的子查询中添加RCMS rsub INNER JOIN RCMS_ITEM risub ON rsub.id_rcms = risub.id_rcms INNER JOIN关联逻辑后，触发SQL错误【156】【S0001】：WHERE关键字附近语法不正确。已确认所有表和字段合法，判定为语法问题。

原有正常SQL #

SELECT PI.QUANTIDADE AS QTD_ORIGINAL, (COALESCE((SELECT SUM(CI.QUANTIDADE) 
            FROM COMPRA_ITEM CI                 INNER JOIN COMPRA C ON (CI.ID_COMPRA = C.ID_COMPRA 
                    AND CI.ID_ORGAO = C.ID_ORGAO 
                    AND CI.ID_EXERCICIO = C.ID_EXERCICIO 
                    and (ci.valor  = lc.vl_unitario  and ci.quantidade = lc.quantidade )
                    AND CI.ID_MATERIAL = PI.ID_MATERIAL) 
                LEFT JOIN CONTABIL_EMPENHO E ON E.ID_COMPRA = C.ID_COMPRA  
                    AND E.ID_ORGAO = C.ID_ORGAO 
                    AND E.ID_EXERCICIO = C.ID_EXERCICIO 
                    AND E.TIPO_DESPESA = 'EOA' 
            WHERE CO.ID_CONTRATO = C.ID_CONTRATO 
                AND CO.ID_ORGAO = C.ID_ORGAO 
                AND (C.EXCLUIDA IS NULL OR C.EXCLUIDA = 'N') 
                AND C.ID_COMPRA > 0 
                AND E.ID_REGEMPENHO IS NULL 
               
                AND CO.ID_CONTRATO = '19882022'), 0)
+ COALESCE((SELECT SUM(RI.QUANTIDADE) 
        FROM RCMS R 
            INNER JOIN RCMS_ITEM RI ON RI.ID_RCMS = R.ID_RCMS 
                AND RI.ID_ORGAO = R.ID_ORGAO 
                AND RI.ID_EXERCICIO = R.ID_EXERCICIO 
        WHERE R.ID_CONTRATO = CO.ID_CONTRATO 
            AND R.ID_ORGAO = CO.ID_ORGAO 
            AND R.EXCLUIDA = 'N' 
            AND RI.ID_MATERIAL = PI.ID_MATERIAL
            AND R.ID_RCMS <> -109456
            AND R.BAIXA = 'N'),0 )) AS QTD_UTILIZADA, 
(COALESCE((SELECT SUM(CI.QUANTIDADE) 
            FROM  COMPRA_ITEM CI 
                INNER JOIN COMPRA C ON (CI.ID_COMPRA = C.ID_COMPRA 
                    AND CI.ID_ORGAO = C.ID_ORGAO 
                    AND CI.ID_EXERCICIO = C.ID_EXERCICIO 
                    AND CI.ID_MATERIAL = PI.ID_MATERIAL) 
            WHERE CO.ID_CONTRATO = C.ID_CONTRATO 
                AND CO.ID_ORGAO = C.ID_ORGAO 
                AND (C.EXCLUIDA IS NULL OR C.EXCLUIDA = 'N') 
                AND C.ID_COMPRA > 0 
                AND CO.ID_CONTRATO = '19882022'), 0)) AS QTD_COMPRADA,  
       PI.ID_MATERIAL, CASE WHEN M.ID_MATERIAL IS NULL THEN PI.DESCRICAO ELSE M.NOME END AS DESCRICAO, 
     LC.VL_UNITARIO AS VL_UNITARIO, F.NOME, LC.ID_FORNECEDOR, CASE WHEN PI.ID_MATERIAL IS NOT NULL THEN M.UNIDADE ELSE PI.UNIDADE END AS UNIDADE, 
     PI.ID_PROCESSO_ITEM, PI.ORDEM 
FROM LICITACAO_PROCESSO LP 
INNER JOIN LICITACAO_COTACAO LC ON LC.ID_PROCESSO = LP.ID_PROCESSO 
    AND LC.ID_MODALIDADE = LP.ID_MODALIDADE 
    AND LC.ID_EXERCICIO = LP.ID_EXERCICIO 
    AND LC.ID_ORGAO = LP.ID_ORGAO 
INNER JOIN LICITACAO_PROCESSO_ITEM PI ON PI.ID_PROCESSO_ITEM = LC.ID_PROCESSO_ITEM 
LEFT JOIN ESTOQUE_MATERIAL M ON M.ID_MATERIAL = PI.ID_MATERIAL 
INNER JOIN CONTABIL_CONTRATO CO ON CO.ID_PROCESSO = LP.PROCESSO 
    AND CO.ID_ORGAO = LP.ID_ORGAO 
    AND CO.ID_FORNECEDOR = LC.ID_FORNECEDOR 
INNER JOIN FORNECEDOR F ON CO.ID_FORNECEDOR = F.ID_FORNECEDOR 
    AND CO.ID_ORGAO = F.ID_ORGAO 
WHERE LP.PROCESSO = '1307/2022'
    AND LC.VENCEDOR = 2 
    AND LP.ID_ORGAO = '020000'

修改后报错的SQL #

SELECT PI.QUANTIDADE AS QTD_ORIGINAL, (COALESCE((SELECT SUM(CI.QUANTIDADE) 
                FROM COMPRA_ITEM CI                 INNER JOIN COMPRA C ON (CI.ID_COMPRA = C.ID_COMPRA 
                        AND CI.ID_ORGAO = C.ID_ORGAO 
                        AND CI.ID_EXERCICIO = C.ID_EXERCICIO 
                        and (ci.valor  = lc.vl_unitario  and ci.quantidade = lc.quantidade )
                        AND CI.ID_MATERIAL = PI.ID_MATERIAL) 
                    LEFT JOIN CONTABIL_EMPENHO E ON E.ID_COMPRA = C.ID_COMPRA  
                        AND E.ID_ORGAO = C.ID_ORGAO 
                        AND E.ID_EXERCICIO = C.ID_EXERCICIO 
                        AND E.TIPO_DESPESA = 'EOA' 
                WHERE CO.ID_CONTRATO = C.ID_CONTRATO 
                    AND CO.ID_ORGAO = C.ID_ORGAO 
                    AND (C.EXCLUIDA IS NULL OR C.EXCLUIDA = 'N') 
                    AND C.ID_COMPRA > 0 
                    AND E.ID_REGEMPENHO IS NULL 
                   
                    AND CO.ID_CONTRATO = '19882022'), 0)
+ COALESCE((SELECT SUM(RI.QUANTIDADE) 
            FROM RCMS R 
                INNER JOIN RCMS_ITEM RI ON RI.ID_RCMS = R.ID_RCMS 
                    AND RI.ID_ORGAO = R.ID_ORGAO 
                    AND RI.ID_EXERCICIO = R.ID_EXERCICIO 
            WHERE R.ID_CONTRATO = CO.ID_CONTRATO 
                AND R.ID_ORGAO = CO.ID_ORGAO 
                AND R.EXCLUIDA = 'N' 
                AND RI.ID_MATERIAL = PI.ID_MATERIAL
                AND R.ID_RCMS <> -109456
                AND R.BAIXA = 'N'),0 )) AS QTD_UTILIZADA, 
(COALESCE((SELECT SUM(CI.QUANTIDADE) 
FROM RCMS rsub inner join rcms_item risub on rsub.id_rcms = risub.id_rcms inner join COMPRA_ITEM CI        --this causes error on the where 
                INNER JOIN COMPRA C ON (CI.ID_COMPRA = C.ID_COMPRA 
                    AND CI.ID_ORGAO = C.ID_ORGAO 
                    AND CI.ID_EXERCICIO = C.ID_EXERCICIO 
                    AND CI.ID_MATERIAL = PI.ID_MATERIAL) 
            WHERE CO.ID_CONTRATO = C.ID_CONTRATO 
                AND CO.ID_ORGAO = C.ID_ORGAO 
                AND (C.EXCLUIDA IS NULL OR C.EXCLUIDA = 'N') 
                AND C.ID_COMPRA > 0 
                AND CO.ID_CONTRATO = '19882022'), 0)) AS QTD_COMPRADA,    
       PI.ID_MATERIAL, CASE WHEN M.ID_MATERIAL IS NULL THEN PI.DESCRICAO ELSE M.NOME END AS DESCRICAO, 
     LC.VL_UNITARIO AS VL_UNITARIO, F.NOME, LC.ID_FORNECEDOR, CASE WHEN PI.ID_MATERIAL IS NOT NULL THEN M.UNIDADE ELSE PI.UNIDADE END AS UNIDADE, 
     PI.ID_PROCESSO_ITEM, PI.ORDEM 
FROM LICITACAO_PROCESSO LP 
INNER JOIN LICITACAO_COTACAO LC ON LC.ID_PROCESSO = LP.ID_PROCESSO 
    AND LC.ID_MODALIDADE = LP.ID_MODALIDADE 
    AND LC.ID_EXERCICIO = LP.ID_EXERCICIO 
    AND LC.ID_ORGAO = LP.ID_ORGAO 
INNER JOIN LICITACAO_PROCESSO_ITEM PI ON PI.ID_PROCESSO_ITEM = LC.ID_PROCESSO_ITEM 
LEFT JOIN ESTOQUE_MATERIAL M ON M.ID_MATERIAL = PI.ID_MATERIAL 
INNER JOIN CONTABIL_CONTRATO CO ON CO.ID_PROCESSO = LP.PROCESSO 
    AND CO.ID_ORGAO = LP.ID_ORGAO 
    AND CO.ID_FORNECEDOR = LC.ID_FORNECEDOR 
INNER JOIN FORNECEDOR F ON CO.ID_FORNECEDOR = F.ID_FORNECEDOR 
    AND CO.ID_ORGAO = F.ID_ORGAO 
WHERE LP.PROCESSO = '1307/2022'
    AND LC.VENCEDOR = 2 
    AND LP.ID_ORGAO = '020000'

错误原因分析 #

修改后的QTD_COMPRADA子查询中，添加RCMS rsub INNER JOIN RCMS_ITEM risub后，直接用INNER JOIN COMPRA_ITEM CI但没有指定risub（或rsub）与CI/C之间的关联条件，导致数据库无法解析这两组表之间的关系，语法结构断裂，进而触发WHERE子句附近的语法错误。

修正后的SQL #

核心是给新增的RCMS/RCMS_ITEM和原有表添加合理的关联条件（以下关联条件为示例，可根据实际业务调整）：

SELECT PI.QUANTIDADE AS QTD_ORIGINAL, (COALESCE((SELECT SUM(CI.QUANTIDADE) 
                FROM COMPRA_ITEM CI                 INNER JOIN COMPRA C ON (CI.ID_COMPRA = C.ID_COMPRA 
                        AND CI.ID_ORGAO = C.ID_ORGAO 
                        AND CI.ID_EXERCICIO = C.ID_EXERCICIO 
                        and (ci.valor  = lc.vl_unitario  and ci.quantidade = lc.quantidade )
                        AND CI.ID_MATERIAL = PI.ID_MATERIAL) 
                    LEFT JOIN CONTABIL_EMPENHO E ON E.ID_COMPRA = C.ID_COMPRA  
                        AND E.ID_ORGAO = C.ID_ORGAO 
                        AND E.ID_EXERCICIO = C.ID_EXERCICIO 
                        AND E.TIPO_DESPESA = 'EOA' 
                WHERE CO.ID_CONTRATO = C.ID_CONTRATO 
                    AND CO.ID_ORGAO = C.ID_ORGAO 
                    AND (C.EXCLUIDA IS NULL OR C.EXCLUIDA = 'N') 
                    AND C.ID_COMPRA > 0 
                    AND E.ID_REGEMPENHO IS NULL 
                   
                    AND CO.ID_CONTRATO = '19882022'), 0)
+ COALESCE((SELECT SUM(RI.QUANTIDADE) 
            FROM RCMS R 
                INNER JOIN RCMS_ITEM RI ON RI.ID_RCMS = R.ID_RCMS 
                    AND RI.ID_ORGAO = R.ID_ORGAO 
                    AND RI.ID_EXERCICIO = R.ID_EXERCICIO 
            WHERE R.ID_CONTRATO = CO.ID_CONTRATO 
                AND R.ID_ORGAO = CO.ID_ORGAO 
                AND R.EXCLUIDA = 'N' 
                AND RI.ID_MATERIAL = PI.ID_MATERIAL
                AND R.ID_RCMS <> -109456
                AND R.BAIXA = 'N'),0 )) AS QTD_UTILIZADA, 
(COALESCE((SELECT SUM(CI.QUANTIDADE) 
FROM RCMS rsub 
    INNER JOIN RCMS_ITEM risub ON rsub.id_rcms = risub.id_rcms 
    INNER JOIN COMPRA_ITEM CI ON risub.ID_MATERIAL = CI.ID_MATERIAL  -- 新增RCMS_ITEM与COMPRA_ITEM的关联
    INNER JOIN COMPRA C ON (CI.ID_COMPRA = C.ID_COMPRA 
            AND CI.ID_ORGAO = C.ID_ORGAO 
            AND CI.ID_EXERCICIO = C.ID_EXERCICIO 
            AND CI.ID_MATERIAL = PI.ID_MATERIAL
            AND rsub.ID_CONTRATO = C.ID_CONTRATO)  -- 新增RCMS与COMPRA的关联
WHERE CO.ID_CONTRATO = C.ID_CONTRATO 
    AND CO.ID_ORGAO = C.ID_ORGAO 
    AND (C.EXCLUIDA IS NULL OR C.EXCLUIDA = 'N') 
    AND C.ID_COMPRA > 0 
    AND CO.ID_CONTRATO = '19882022'), 0)) AS QTD_COMPRADA,    
       PI.ID_MATERIAL, CASE WHEN M.ID_MATERIAL IS NULL THEN PI.DESCRICAO ELSE M.NOME END AS DESCRICAO, 
     LC.VL_UNITARIO AS VL_UNITARIO, F.NOME, LC.ID_FORNECEDOR, CASE WHEN PI.ID_MATERIAL IS NOT NULL THEN M.UNIDADE ELSE PI.UNIDADE END AS UNIDADE, 
     PI.ID_PROCESSO_ITEM, PI.ORDEM 
FROM LICITACAO_PROCESSO LP 
INNER JOIN LICITACAO_COTACAO LC ON LC.ID_PROCESSO = LP.ID_PROCESSO 
    AND LC.ID_MODALIDADE = LP.ID_MODALIDADE 
    AND LC.ID_EXERCICIO = LP.ID_EXERCICIO 
    AND LC.ID_ORGAO = LP.ID_ORGAO 
INNER JOIN LICITACAO_PROCESSO_ITEM PI ON PI.ID_PROCESSO_ITEM = LC.ID_PROCESSO_ITEM 
LEFT JOIN ESTOQUE_MATERIAL M ON M.ID_MATERIAL = PI.ID_MATERIAL 
INNER JOIN CONTABIL_CONTRATO CO ON CO.ID_PROCESSO = LP.PROCESSO 
    AND CO.ID_ORGAO = LP.ID_ORGAO 
    AND CO.ID_FORNECEDOR = LC.ID_FORNECEDOR 
INNER JOIN FORNECEDOR F ON CO.ID_FORNECEDOR = F.ID_FORNECEDOR 
    AND CO.ID_ORGAO = F.ID_ORGAO 
WHERE LP.PROCESSO = '1307/2022'
    AND LC.VENCEDOR = 2 
    AND LP.ID_ORGAO = '020000'

内容的提问来源于stack exchange，提问作者Diego Alves