Mas a query roda no 1.5? A query sem a criação da View... Tenta esse também: http://pastebin.com/jFqEbr9z
Esse último, a ideia foi remover o cross join e o union... tem que ver se produz os mesmos resultados. Em qui, 23 de jun de 2016 às 18:10, Israel Pinheiro israel.ip...@gmail.com [firebird-support] <firebird-support@yahoogroups.com> escreveu: > > > Roda certo no 2.5. A View funciona corretamente na aplicação com o 2.5 > porém tenho dois clientes que estão com FB 1.5 e não consigo criar a View > > Em 23/06/2016 18:07, "Gabriel Frones grfro...@gmail.com > [firebird-support]" <firebird-support@yahoogroups.com> escreveu: > >> >> >> Eu preciso de informações sobre suas tabelas (essas envolvidas) e que >> tipo de relatório essa view fornece pra te dar uma query que funcione. Mas >> uma dica geral a principio é: isole o problema. Primeiro construa uma query >> que funcione, depois tente criar uma view com ela. >> >> Essa query não roda? http://pastebin.com/3RpkW408 >> >> Em qui, 23 de jun de 2016 às 18:00, 'Israel Pinheiro' >> israel.ip...@gmail.com [firebird-support] < >> firebird-support@yahoogroups.com> escreveu: >> >>> >>> >>> Gabriel, boa noite, não estou conseguindo fazer... Tem como me auxiliar >>> por favor >>> >>> >>> >>> *De:* firebird-support@yahoogroups.com [mailto: >>> firebird-support@yahoogroups.com] >>> *Enviada em:* quinta-feira, 23 de junho de 2016 17:56 >>> *Para:* firebird-support@yahoogroups.com >>> *Assunto:* Re: [firebird-support] It works on FB 2.5 but does not work >>> in FB 1.5 >>> >>> >>> >>> >>> >>> Israel, >>> >>> >>> >>> the error is because cross joins (from a, b) are only supported with FB >>> 2.0+. See here: http://firebirdsql.org/refdocs/langrefupd20-select.html >>> >>> >>> >>> But your problem is greater than that: I'm pretty sure (from my >>> knowledge of sql and by reading the field names - I'm brazilian too) your >>> query is a little messy. First, you use the cross join syntax (from a, b) >>> but you're not using a real cross join (because of the where clause >>> relating them). Second, your inner joins look to me as if they should be >>> outer (left) joins. And third, I believe the union is unnecessary... maybe >>> some coalesces would achieve the same result with less overhead. >>> >>> >>> >>> Perhaps if you could explain your tables' structure and your goal, we >>> could help you better. >>> >>> >>> >>> You can however stick to this query and just replace the cross join with >>> a left join. >>> >>> Em qui, 23 de jun de 2016 às 16:51, 'Israel Pinheiro' >>> israel.ip...@gmail.com [firebird-support] < >>> firebird-support@yahoogroups.com> escreveu: >>> >>> >>> >>> >>> >>> I created a view that works perfectly on FB 2.5, but now need to create >>> the >>> same view on FB 1.5, but appears error in creation. Can help me? >>> >>> CREATE OR ALTER VIEW ESPELHO( >>> >>> DTMOVPROD, >>> >>> TIPOMOV, >>> >>> CODNAT, >>> >>> CODVENDA, >>> >>> CODCOMPRA, >>> >>> CODPROD, >>> >>> DESCPROD, >>> >>> CODFISC, >>> >>> TIPOMOVPROD, >>> >>> DOCMOVPROD, >>> >>> CODALMOX, >>> >>> CODLOTE, >>> >>> QTDMOVPROD, >>> >>> PRECOMOVPROD, >>> >>> ESTOQMOVPROD, >>> >>> SLDMOVPRODAX, >>> >>> TIPOPROD, >>> >>> CUSTOMPMMOVPRODAX, >>> >>> SLDMOVPROD, >>> >>> CUSTOMPMMOVPROD, >>> >>> CODMOVPROD, >>> >>> CODEMPTM, >>> >>> CODEMP, >>> >>> CODFILIALTM, >>> >>> CODFILIAL, >>> >>> SALDO, >>> >>> NOME) >>> >>> 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.PRECOMOVPROD, >>> >>> MP.ESTOQMOVPROD, >>> >>> MP.SLDMOVPRODAX, >>> >>> EQ.TIPOPROD, >>> >>> MP.CUSTOMPMMOVPRODAX, >>> >>> MP.SLDMOVPROD, >>> >>> MP.CUSTOMPMMOVPROD, >>> >>> MP.CODMOVPROD, >>> >>> MP.CODEMPTM, >>> >>> TM.CODEMP, >>> >>> MP.CODFILIALTM, >>> >>> TM.CODFILIAL, >>> >>> (MP.sldmovprod*MP.CUSTOMPMMOVPROD) SALDO, >>> >>> CLI.NOMECLI >>> >>> CODTIPOMOV >>> >>> 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 >>> >>> where MP.CODEMPTM=TM.CODEMP AND MP.CODFILIALTM=TM.CODFILIAL AND >>> >>> MP.CODTIPOMOV=TM.CODTIPOMOV AND tipoprod='P' and mp.codcompra is null >>> >>> 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.PRECOMOVPROD, >>> >>> MP.ESTOQMOVPROD, >>> >>> MP.SLDMOVPRODAX, >>> >>> EQ.TIPOPROD, >>> >>> MP.CUSTOMPMMOVPRODAX, >>> >>> MP.SLDMOVPROD, >>> >>> MP.CUSTOMPMMOVPROD, >>> >>> MP.CODMOVPROD, >>> >>> MP.CODEMPTM, >>> >>> TM.CODEMP, >>> >>> MP.CODFILIALTM, >>> >>> TM.CODFILIAL, >>> >>> (MP.sldmovprod*MP.CUSTOMPMMOVPROD) SALDO, >>> >>> FORN.RAZFOR >>> >>> CODTIPOMOV >>> >>> 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 >>> >>> where MP.CODEMPTM=TM.CODEMP AND MP.CODFILIALTM=TM.CODFILIAL AND >>> >>> MP.CODTIPOMOV=TM.CODTIPOMOV AND tipoprod='P' and mp.codvenda is null >>> >>> ; >>> >>> --- >>> Este email foi escaneado pelo Avast antivírus. >>> https://www.avast.com/antivirus >>> >>> [Non-text portions of this message have been removed] >>> >>> >>> >>> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient> >>> Livre >>> de vírus. www.avast.com >>> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>. >>> >>> >>> >
