Thank you thank you very much.
The indicated solution solved perfectly. Thanks to all, especially to Gabriel
for their time.
God bless all.
<code>
CREATE 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,
CAST((MP.sldmovprod*MP.CUSTOMPMMOVPROD) AS NUMERIC(15, 2)) SALDO,
cast(CLI.NOMECLI as char(60))
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,
CAST((MP.sldmovprod*MP.CUSTOMPMMOVPROD) AS NUMERIC(15, 2)) 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
</code>
De: [email protected] [mailto:[email protected]]
Enviada em: quinta-feira, 23 de junho de 2016 17:56
Para: [email protected]
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' [email protected]
<mailto:[email protected]> [firebird-support]
<[email protected] <mailto:[email protected]> >
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]
---
Este email foi escaneado pelo Avast antivírus.
https://www.avast.com/antivirus
