Subject Re: [firebird-support] It works on FB 2.5 but does not work in FB 1.5
Author Gabriel Frones
Post date 2016-06-23T20:55:31Z
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.ipnet@... [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]