sorry, I'm wrong here... the cross join syntax is new in FB 2.0+, but the comma syntax is already there.
Em qui, 23 de jun de 2016 às 17:55, Gabriel Frones <grfro...@gmail.com> escreveu: > 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] >> >> >> >
