Adding an inner join in a subquery gives a strange error in where of a subquery

I have the following query

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'

If I add this RCMS rsub inner join rcms_item risub on rsub.id_rcms = risub.id_rcms inner join to the subquery of qtd_comprada it gives an error

SQL Error [156] [S0001]: incorrect syntax near keyword ‘WHERE’.

The query that I am trying to execute is like this

    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'

    
    

I place a comment in the second query where I am trying to add an inner join and not being able.

All columns and table are ok, it’s a syntax error… apparently

>Solution :

Just off the bat this is missing an "ON" condition. It’s just a floating join.

inner join COMPRA_ITEM CI ... --this causes error on the where 

(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)