-2147217900 [ma-3.1.7][10.6.3-MariaDB]You have an error in your SQL
syntax; check the manual that corresponds to your MariaDB server version
for the right syntax to use near '0 ),
WHERE JPPEDIDO.IDPEDIDO = nIdPedido;
END IF;
END' at line 200
Full SQL:
CREATE PROCEDURE ze_Pedidocalculo( nIdPedido INT, nRotina INT(11) )
/*
nValAdi, nPreNot, nValPro -> variaveis destruidas apos execucao
*/
THIS: BEGIN
DECLARE nValPro DECIMAL(16,2) DEFAULT 0;
IF COALESCE( nIdPedido, 0 ) = 0 THEN
LEAVE THIS;
END IF;
/* soma tudo pra rateio de valores nao oficiais (por fora) */
IF nRotina = 1 THEN
SET nValPro = COALESCE( ( SELECT SUM( IPQTDE * IPPREPED ) FROM JPITPED WHERE IPPEDIDO = nIdPedido ), 0 );
/* rateio/calculos nao oficiais (por fora) */
UPDATE JPITPED
INNER JOIN JPPEDIDO ON IDPEDIDO = IPPEDIDO
SET
IPVALADI = ( @nValAdi := ROUND( PDVALADI / IF( nValPro = 0, 1, nValPro ) * ( IPPREPED * IPQTDE ), 2 ) ),
IPPRENOT = ( @nPreNot := ROUND( IF( IPQTDE = 0, 0, ( ( IPPREPED + @nValAdi ) * IPQTDE ) )
* ( ( 100 - JPPEDIDO.PDPERDES ) / 100 ) * ( ( 100 + JPPEDIDO.PDPERADI ) / 100 )
/ IF( IPQTDE = 0, 1, IPQTDE ), 5 ) ),
IPVALPRO = ROUND( @nPreNot * IPQTDE, 2 ),
IPVALNOT = ROUND( @nPreNot * IPQTDE, 2 )
WHERE IPPEDIDO = nIdPedido;
END IF;
/* soma novos valores */
IF nRotina = 2 THEN
UPDATE JPPEDIDO
SET PDVALPRO = COALESCE( (
SELECT SUM( IPVALPRO )
FROM JPITPED
WHERE IPPEDIDO = nIdPedido
), 0 )
WHERE IDPEDIDO = nIdPedido;
END IF;
/* calculo de rateios oficiais */
IF nRotina = 3 THEN
UPDATE JPITPED
INNER JOIN JPPEDIDO ON IDPEDIDO = IPPEDIDO
SET
IPVALFRE = ROUND( PDVALFRE * IPVALPRO / IF( PDVALPRO = 0, 1, PDVALPRO ), 2 ),
IPVALSEG = ROUND( PDVALSEG * IPVALPRO / IF( PDVALPRO = 0, 1, PDVALPRO ), 2 ),
IPVALOUT = ROUND( PDVALOUT * IPVALPRO / IF( PDVALPRO = 0, 1, PDVALPRO ), 2 ),
IPVALEXT = ROUND( PDVALEXT * IPVALPRO / IF( PDVALPRO = 0, 1, PDVALPRO ), 2 ),
IPVALDES = ROUND( PDVALDES * IPVALPRO / IF( PDVALPRO = 0, 1, PDVALPRO ), 2 )
/* IPVALADI = ROUND( PDVALADI * IPVALPRO / IF( PDVALPRO = 0, 1, PDVALPRO ), 2 ) */
WHERE JPITPED.IPPEDIDO = nIdPedido;
END IF;
/* resoma pedido */
IF nRotina = 4 THEN
UPDATE JPPEDIDO
SET PDVALNOT = COALESCE(
( SELECT SUM( IPVALNOT )
FROM JPITPED
WHERE IPPEDIDO = nIdPedido ), 0 )
WHERE IDPEDIDO = nIdPedido;
END IF;
/* aplica regra de tributação */
IF nRotina = 6 THEN
UPDATE JPITPED
LEFT JOIN JPPEDIDO ON IDPEDIDO = IPPEDIDO
LEFT JOIN JPCADASTRO ON IDCADASTRO = PDCADASTRO
LEFT JOIN JPITEM ON IDPRODUTO = IPPRODUTO
LEFT JOIN JPTABUF ON IDUF = CDUFENT
LEFT JOIN JPTRANSACAO ON IDTRANSACAO = PDTRANSACAO
LEFT JOIN JPIMPOSTO
ON IMTRANSACAO = PDTRANSACAO
AND IMTRIUF = UFTRIUF
AND IMTRICAD = CDTRICAD
AND IMTRIPRO = IETRIPRO
LEFT JOIN JPTABIBPT ON IBCODIGO = JPITEM.IENCM
SET
IPCFOP = COALESCE( IMCFOP, '' ),
IPIIALI = COALESCE( IMIIALI, 0 ),
IPISSALI = COALESCE( IMISSALI, 0 ),
IPIPICST = COALESCE( IMIPICST, '' ),
IPIPIALI = COALESCE( IMIPIALI, 0 ),
IPIPIICM = COALESCE( IMIPIICM, '' ),
IPIPIENQ = COALESCE( IMIPIENQ, '' ),
IPICMCST = COALESCE( IMICMCST, '' ),
IPICMALI = COALESCE( IMICMALI, 0 ),
IPICMRED = COALESCE( IMICMRED, 0 ),
IPFCPALI = COALESCE( IMFCPALI, 0 ),
IPSUBALI = COALESCE( IMSUBALI, 0 ),
IPSUBIVA = COALESCE( IMSUBIVA, 0 ),
IPSUBRED = COALESCE( IMSUBRED, 0 ),
IPDIFCAL = COALESCE( IMDIFCAL, '' ),
IPDIFALII = COALESCE( IMDIFALII, 0 ),
IPDIFALIU = COALESCE( IMDIFALIU, 0 ),
IPDIFALIF = COALESCE( IMDIFALIF, 0 ),
IPPISCST = COALESCE( IMPISCST, 0 ),
IPPISALI = COALESCE( IMPISALI, 0 ),
IPPISENQ = COALESCE( IMPISENQ, '' ),
IPCOFCST = COALESCE( IMCOFCST, '' ),
IPCOFALI = COALESCE( IMCOFALI, 0 ),
IPCOFENQ = COALESCE( IMCOFENQ, '' ),
IPICSALI = COALESCE( IMICSALI, 0 ),
IPLEIS = COALESCE( IMLEIS, '' ),
IPTRIBUT = COALESCE( IDIMPOSTO, 0 )
WHERE IDPEDIDO = nIdPedido AND IPTRIBUT <> 999999;
END IF;
/* ajuste complementar */
IF nRotina = 7 THEN
UPDATE JPITPED
LEFT JOIN JPPEDIDO ON IDPEDIDO = IPPEDIDO
LEFT JOIN JPTRANSACAO ON IDTRANSACAO = PDTRANSACAO
LEFT JOIN JPITEM ON IDPRODUTO = IPPRODUTO
LEFT JOIN JPTABIBPT ON IBCODIGO = IENCM
SET
IPIMPALI = IF( TRREACAO LIKE '%CONSUMIDOR%',
IF( LEFT( IPICMCST, 1 ) IN ( '0', '3', '4', '5' ),
COALESCE( IBNACALI, 0 ), COALESCE( IBIMPALI, 0 ) ), 0 )
WHERE IPPEDIDO = nIdPedido;
END IF;
/* prepara cálculo de impostos */
IF nRotina = 8 THEN
UPDATE JPITPED
SET
IPIIBAS = IF( IPIIALI > 0, IPVALPRO, 0 ),
IPIIVAL = IF( IPIIALI > 0, FLOOR( IPVALPRO * IPIIALI ) / 100, 0 ),
IPISSBAS = IF( IPISSALI > 0, IPVALPRO + IPIIVAL, 0 ),
IPISSVAL = IF( IPISSALI > 0, FLOOR( ( IPVALPRO + IPIIVAL ) * IPISSALI ) / 100, 0 )
WHERE IPPEDIDO = nIdPedido;
END IF;
IF nRotina = 99 THEN
UPDATE JPPEDIDO
JOIN
( SELECT
SUM( IPISSBAS ) AS ISSBAS,
SUM( IPISSVAL ) AS ISSVAL,
SUM( IPIIBAS ) AS IIBAS,
SUM( IPIIVAL ) AS IIVAL,
SUM( IPIPIBAS ) AS IPIBAS,
SUM( IPIPIVAL ) AS IPIVAL,
SUM( IPICMBAS ) AS ICMBAS,
SUM( IPICMVAL ) AS ICMVAL,
SUM( IPFCPVAL ) AS FCPVAL,
SUM( IPSUBBAS ) AS SUBBAS,
SUM( IPSUBVAL ) AS SUBVAL,
SUM( IPDIFVALI ) AS DIFVALI,
SUM( IPDIFVALF ) AS DIFVALF,
SUM( IPPISBAS ) AS PISBAS,
SUM( IPPISVAL ) AS PISVAL,
SUM( IPCOFBAS ) AS COFBAS,
SUM( IPCOFVAL ) AS COFVAL,
SUM( IPVALNOT ) AS VALNOT,
SUM( IPICSBAS ) AS ICSBAS,
SUM( IPICSVAL ) AS ICSVAL,
SUM( IPVALADU ) AS VALADU,
SUM( IPVALIOF ) AS VALIOF,
SUM( IPIMPVAL ) AS IMPVAL,
SUM( IPVALCUS ) AS VALCUS,
AVG( IPICSALI ) AS ICSALI
FROM JPITPED
WHERE IPPEDIDO = nIdPedido
GROUP BY IPPEDIDO ) AS TEMP
SET
PDISSBAS = COALESCE( TEMP.ISSBAS, 0 ),
PDISSVAL = COALESCE( TEMP.ISSVAL, 0 ),
PDIIBAS = COALESCE( TEMP.IIBAS, 0 ),
PDIIVAL = COALESCE( TEMP.IIVAL, 0 ),
PDIPIBAS = COALESCE( TEMP.IPIBAS, 0 ),
PDIPIVAL = COALESCE( TEMP.IPIVAL, 0 ),
PDICMBAS = COALESCE( TEMP.ICMBAS, 0 ),
PDICMVAL = COALESCE( TEMP.ICMVAL, 0 ),
PDFCPVAL = COALESCE( TEMP.FCPVAL, 0 ),
PDSUBBAS = COALESCE( TEMP.SUBBAS, 0 ),
PDSUBVAL = COALESCE( TEMP.SUBVAL, 0 ),
PDDIFVALI = COALESCE( TEMP.DIFVALI, 0 ),
PDDIFVALF = COALESCE( TEMP.DIFVALF, 0 ),
PDPISBAS = COALESCE( TEMP.PISBAS, 0 ),
PDPISVAL = COALESCE( TEMP.PISVAL, 0 ),
PDCOFBAS = COALESCE( TEMP.COFBAS, 0 ),
PDCOFVAL = COALESCE( TEMP.COFVAL, 0 ),
PDVALNOT = COALESCE( TEMP.VALNOT, 0 ),
PDICSBAS = COALESCE( TEMP.ICSBAS, 0 ),
PDICSVAL = COALESCE( TEMP.ICSVAL, 0 ),
PDVALADU = COALESCE( TEMP.VALADU, 0 ),
PDVALIOF = COALESCE( TEMP.VALIOF, 0 ),
PDIMPVAL = COALESCE( TEMP.IMPVAL, 0 ),
PDVALCUS = COALESCE( TEMP.VALCUS, 0 ),
PDICSALI = COALESCE( TEMP.ICSALI 0 ),
WHERE JPPEDIDO.IDPEDIDO = nIdPedido;
END IF;
END
;
Called from ADOCLASS:EXECUTECMD(262)
Called from UPDATESQL(1488)
Called from UPDATE2020(29)
Called from ZE_UPDATE(121)
Called from SISTEMA(61)
Called from (b)MAIN(75)
Pra este, do cadastramento, já peguei as manhas.... lembrando que ele fica dentro do sql.XML, tudo misturado.
Acusou linha 200.
Pelo meu EDITOR DE TEXTO, vou lá no XML aonde começa a rotina.
adiciono 200, e vou ao ponto que foi referenciado.
No momento dividi em chamar rotina passando 1,2,3,4,5, executando uma parte de cada vez enquanto está em testes.
A propósito... o erro foi aqui:
como alterei muito coalesce() de uma vez, coloquei vÃrgula até no último, antes do WHERE.