Fórum Consulta Firebird retorna valores duplicados #574793

CREATE OR ALTER VIEW ESPELHO(
    DTMOVPROD,
    TIPOMOV,
    CODNAT,
    CODVENDA,
    CODCOMPRA,
    CODPROD,
    DESCPROD,
    CODFISC,
    TIPOMOVPROD,
    DOCMOVPROD,
    CODALMOX,
    CODLOTE,
    QTDMOVPROD,
    QTDMOVPRODENT,
    QTDMOVPRODSAI,
    PRECOMOVPRODENT,
    PRECOMOVPRODSAI,
    PRECOMEDIOMOVENT,
    PRECOMEDIOMOVSAI,
    ESTOQMOVPROD,
    SLDMOVPRODAX,
    TIPOPROD,
    CUSTOMPMMOVPRODAX,
    SLDMOVPROD,
    ESTOQUE,
    CUSTOMPMMOVPROD,
    CODMOVPROD,
    CODEMPTM,
    CODEMP,
    CODFILIALTM,
    CODFILIAL,
    CODIGO,
    NOME,
    SALDO,
    TOTAL,
    TOTALENT,
    TOTALSAI,
    TOTALCUSTO,
    TOTALCUSTOENT,
    TOTALCUSTOSAI,
    CODMOV,
    INSERIDO,
    CUSTOMPMPROD,
    PRECO,
    SOMAENT,
    SOMASAI,
    SLDPROD)
AS
SELECT
  MP.DTMOVPROD,
  TM.TIPOMOV,
  MP.CODNAT,
  MP.CODVENDA,
  MP.CODCOMPRA,
  MP.CODPROD,
  EQ.DESCPROD,
  EQ.CODFISC,
  MP.TIPOMOVPROD,
  MP.DOCMOVPROD,
  MP.CODALMOX,
  MP.CODLOTE,
  MP.QTDMOVPROD,
  MP.QTDMOVPROD,
  MP.QTDMOVPROD*0,
  MP.PRECOMOVPROD,
  MP.PRECOMOVPROD*0,
  MP.CUSTOMPMMOVPROD,  /* PRECOMEDIOPRODMOVENT */
  MP.CUSTOMPMMOVPROD*0, /* PRECOMEDIOPRODMOVSAI */
  MP.ESTOQMOVPROD,
  MP.SLDMOVPRODAX,
  EQ.TIPOPROD,
  MP.CUSTOMPMMOVPRODAX,
  MP.SLDMOVPROD,
  CAST(MP.SLDMOVPROD AS NUMERIC(15, 2)) AS ESTOQUE,
  MP.CUSTOMPMMOVPROD,
  MP.CODMOVPROD,
  MP.CODEMPTM,
  TM.CODEMP,
  MP.CODFILIALTM,
  TM.CODFILIAL,
  FORN.CODFOR,
  FORN.RAZFOR AS NOME,
  CAST((MP.SLDMOVPROD * MP.CUSTOMPMMOVPROD) AS NUMERIC(15, 2)) AS SALDO,
  CAST((MP.QTDMOVPROD * MP.PRECOMOVPROD) AS NUMERIC(15,2)) AS TOTAL,
  CAST((MP.QTDMOVPROD * MP.PRECOMOVPROD) AS NUMERIC(15,2)) AS TOTALENT,
  CAST((MP.SLDMOVPROD * 0) AS NUMERIC(15,2)) AS TOTALSAI,
  CAST((MP.QTDMOVPROD * MP.CUSTOMPMMOVPROD) AS NUMERIC(15,2)) AS TOTALCUSTO,
  CAST((MP.QTDMOVPROD * MP.CUSTOMPMMOVPROD) AS NUMERIC(15,2)) AS TOTALCUSTOENT,
  CAST((MP.SLDMOVPROD * 0) AS NUMERIC(15,2)) AS TOTALCUSTOSAI,
  MP.CODTIPOMOV,
  MP.IDUSUINS,
  EQ.CUSTOMPMPROD,
  sum(CPITCOMPRA.PRECOITCOMPRA) AS PRECO,
  CAST((MP.QTDMOVPROD) AS NUMERIC(15,2)) AS SOMAENT,
  CAST((MP.QTDMOVPROD * 0) AS NUMERIC(15,2)) AS SOMASAI,
  ES.SLDPROD
FROM
  EQMOVPROD MP, EQTIPOMOV TM
  INNER JOIN EQPRODUTO EQ ON MP.CODPROD = EQ.CODPROD
  INNER JOIN CPCOMPRA  CP ON MP.CODCOMPRA = CP.CODCOMPRA
  INNER JOIN CPFORNECED FORN ON CP.CODFOR = FORN.CODFOR
  INNER JOIN CPITCOMPRA ON CP.CODEMP = CPITCOMPRA.CODEMP
  AND CP.CODFILIAL = CPITCOMPRA.CODFILIAL
  AND CP.CODCOMPRA = CPITCOMPRA.CODCOMPRA
  AND CPITCOMPRA.CODEMPPD = EQ.CODEMP
  AND CPITCOMPRA.CODFILIALPD = EQ.CODFILIAL
  AND CPITCOMPRA.CODPROD = EQ.CODPROD
  INNER JOIN EQSALDOPROD ES ON EQ.CODEMP = ES.CODEMP
  AND EQ.CODFILIAL = ES.CODFILIAL
  AND EQ.CODPROD = ES.CODPROD
WHERE
  MP.CODEMPTM = TM.CODEMP AND
  MP.CODFILIALTM = TM.CODFILIAL AND 
  MP.CODTIPOMOV = TM.CODTIPOMOV AND 
  TIPOPROD = 'P' AND 
  MP.CODVENDA IS NULL
GROUP BY
  MP.DOCMOVPROD,
  MP.DTMOVPROD,
  TM.TIPOMOV,
  MP.CODNAT,
  MP.CODTIPOMOV,
  MP.CODPROD,
  EQ.DESCPROD,
  MP.CODCOMPRA,
  MP.CODVENDA,
  EQ.CODFISC,
  MP.TIPOMOVPROD,
  MP.CODALMOX,
  MP.CODLOTE,
  MP.QTDMOVPROD,
  MP.PRECOMOVPROD,
  MP.ESTOQMOVPROD,
  MP.SLDMOVPRODAX,
  EQ.TIPOPROD,
  MP.CUSTOMPMMOVPRODAX,
  MP.SLDMOVPROD,
  MP.CUSTOMPMMOVPROD,
  MP.CODMOVPROD,
  MP.CODEMPTM,
  TM.CODEMP,
  MP.CODFILIALTM,
  TM.CODFILIAL,
  FORN.CODFOR,
  FORN.RAZFOR,
  EQ.CUSTOMPMPROD,
  MP.IDUSUINS,
  ES.SLDPROD

UNION ALL

SELECT
  MP.DTMOVPROD,
  TM.TIPOMOV,
  MP.CODNAT,
  MP.CODVENDA,
  MP.CODCOMPRA,
  MP.CODPROD,
  EQ.DESCPROD,
  EQ.CODFISC,
  MP.TIPOMOVPROD,
  MP.DOCMOVPROD,
  MP.CODALMOX,
  MP.CODLOTE,
  MP.QTDMOVPROD,
  MP.QTDMOVPROD*0,
  MP.QTDMOVPROD,
  MP.PRECOMOVPROD*0,
  MP.PRECOMOVPROD,
  MP.CUSTOMPMMOVPROD*0,  /* PRECOMEDIOPRODMOVENT */
  MP.CUSTOMPMMOVPROD, /* PRECOMEDIOPRODMOVSAI */  
  MP.ESTOQMOVPROD,
  MP.SLDMOVPRODAX,
  EQ.TIPOPROD,
  MP.CUSTOMPMMOVPRODAX,
  MP.SLDMOVPROD,
  CAST(MP.SLDMOVPROD AS NUMERIC(15, 2)) AS ESTOQUE,
  MP.CUSTOMPMMOVPROD,
  MP.CODMOVPROD,
  MP.CODEMPTM,
  TM.CODEMP,
  MP.CODFILIALTM,
  TM.CODFILIAL,
  CLI.CODCLI,
  cast(CLI.NOMECLI as char(60)) AS NOMECLI,
  CAST((MP.SLDMOVPROD * MP.CUSTOMPMMOVPROD) AS NUMERIC(15, 2)) AS SALDO,
  CAST((MP.QTDMOVPROD * MP.PRECOMOVPROD) AS NUMERIC(15,2)) AS TOTAL,
  CAST((MP.SLDMOVPROD * 0) AS NUMERIC(15,2)) AS TOTALENT,  
  CAST((MP.QTDMOVPROD * MP.PRECOMOVPROD) AS NUMERIC(15,2)) AS TOTALSAI,
  CAST((MP.QTDMOVPROD * MP.CUSTOMPMMOVPROD) AS NUMERIC(15,2)) AS TOTALCUSTO,
  CAST((MP.SLDMOVPROD * 0) AS NUMERIC(15,2)) AS TOTALCUSTOENT,
  CAST((MP.QTDMOVPROD * MP.CUSTOMPMMOVPROD) AS NUMERIC(15,2)) AS TOTALCUSTOSAI,
  MP.CODTIPOMOV,
  MP.IDUSUINS,
  EQ.CUSTOMPMPROD,
  sum(VDITVENDA.PRECOITVENDA) AS PRECO,
  CAST((MP.QTDMOVPROD*0) AS NUMERIC(15,2)) AS SOMAENT,
  CAST((MP.QTDMOVPROD) AS NUMERIC(15,2)) AS SOMASAI,
  ES.SLDPROD
FROM
  EQMOVPROD MP, EQTIPOMOV TM
  INNER JOIN EQPRODUTO EQ ON MP.CODPROD = EQ.CODPROD
  INNER JOIN VDVENDA VD ON MP.CODVENDA = VD.CODVENDA
  INNER JOIN VDCLIENTE CLI ON VD.CODCLI = CLI.CODCLI
  INNER JOIN VDITVENDA ON VD.CODEMP = VDITVENDA.CODEMP
  AND VD.CODFILIAL = VDITVENDA.CODFILIAL
  AND VD.TIPOVENDA = VDITVENDA.TIPOVENDA
  AND VD.CODVENDA = VDITVENDA.CODVENDA
  AND VDITVENDA.CODEMPPD = EQ.CODEMP
  AND VDITVENDA.CODFILIALPD = EQ.CODFILIAL
  AND VDITVENDA.CODPROD = EQ.CODPROD
  INNER JOIN EQSALDOPROD ES ON EQ.CODEMP = ES.CODEMP
  AND EQ.CODFILIAL = ES.CODFILIAL
  AND EQ.CODPROD = ES.CODPROD
WHERE
  MP.CODEMPTM = TM.CODEMP AND
  MP.CODFILIALTM = TM.CODFILIAL AND 
  MP.CODTIPOMOV = TM.CODTIPOMOV AND 
  TIPOPROD = 'P' AND 
  MP.CODCOMPRA IS NULL
GROUP BY
  MP.DOCMOVPROD,
  MP.DTMOVPROD,
  TM.TIPOMOV,
  MP.CODNAT,
  MP.CODTIPOMOV,
  MP.CODPROD,
  EQ.DESCPROD,
  MP.CODCOMPRA,
  MP.CODVENDA,
  EQ.CODFISC,
  MP.TIPOMOVPROD,
  MP.CODALMOX,
  MP.CODLOTE,
  MP.QTDMOVPROD,
  MP.PRECOMOVPROD,
  MP.ESTOQMOVPROD,
  MP.SLDMOVPRODAX,
  EQ.TIPOPROD,
  MP.CUSTOMPMMOVPRODAX,
  MP.SLDMOVPROD,
  MP.CUSTOMPMMOVPROD,
  MP.CODMOVPROD,
  MP.CODEMPTM,
  TM.CODEMP,
  MP.CODFILIALTM,
  TM.CODFILIAL,
  CLI.CODCLI,
  CLI.NOMECLI,
  EQ.CUSTOMPMPROD,
  MP.IDUSUINS,
  ES.SLDPROD
;