[firebird-support] VIEW CREATES ON FB 2.5 BUT DO NOT CREATE ON FB 1.5

['Israel Pinheiro' [e-mail] [firebird-support]]

Please,



CREATE OR ALTER VIEW ESPELHO12(

CODEMPRESA,

CODFILIAL,

DTMOVPROD,

CODMOVIMENTO,

CODPRODUTO,

TIPOMOVIMENTO,

QUANTIDADE,

SALDO,

CFOP,

CODCOMPRA,

CODVENDA,

DESCRICAO,

NCM,

DOCUMENTO,

TIPOPRODUTO,

CUSTO,

TMEMPRESA,

TMFILIAL,

NOMERAZAOTIPO

)

AS

SELECT

MP.CODEMP,

MP.CODFILIAL,

MP.DTMOVPROD,

MP.CODMOVPROD,

MP.CODPROD,

MP.CODTIPOMOV,

MP.QTDMOVPROD,

MP.SLDMOVPROD,

MP.CODNAT,

MP.CODCOMPRA,

MP.CODVENDA,

EQ.DESCPROD,

EQ.CODFISC,

MP.DOCMOVPROD,

EQ.TIPOPROD,

CAST((MP.sldmovprod*EQ.CUSTOINFOPROD) AS NUMERIC(15,2)) CUSTO,

TM.CODEMP AS TMEMP,

TM.CODFILIAL AS TMFIL,

TM.DESCTIPOMOV

FROM EQMOVPROD MP

INNER JOIN EQPRODUTO eq on mp.codprod = eq.codprod

LEFT JOIN eqtipomov tm on tm.codtipomov = mp.codtipomov

where MP.CODEMP=TM.CODEMP AND MP.CODFILIAL=TM.CODFILIAL AND

MP.CODTIPOMOV=TM.CODTIPOMOV AND tipoprod='P' and mp.codvenda is null and
mp.codcompra is null



GROUP BY

MP.DTMOVPROD,

MP.CODTIPOMOV,

MP.CODMOVPROD,

MP.CODPROD,

MP.CODNAT,

MP.DOCMOVPROD,

MP.CODCOMPRA,

MP.CODVENDA,

EQ.DESCPROD,

EQ.TIPOPROD,

MP.CODEMP,

MP.CODFILIAL,

TM.CODEMP,

TM.CODFILIAL,

MP.QTDMOVPROD,

MP.SLDMOVPROD,

EQ.CODFISC,

EQ.CUSTOINFOPROD,

TM.DESCTIPOMOV







UNION ALL

SELECT

MP.CODEMP,

MP.CODFILIAL,

MP.DTMOVPROD,

MP.CODMOVPROD,

MP.CODPROD,

MP.CODTIPOMOV,

MP.QTDMOVPROD,

MP.SLDMOVPROD,

MP.CODNAT,

MP.CODCOMPRA,

MP.CODVENDA,

EQ.DESCPROD,

EQ.CODFISC,

MP.DOCMOVPROD,

EQ.TIPOPROD,

CAST((MP.sldmovprod*EQ.CUSTOINFOPROD) AS NUMERIC(15,2)) CUSTO,

TM.CODEMP AS TMEMP,

TM.CODFILIAL AS TMFIL,

FORN.RAZFOR AS NOMEFORNECEDOR

FROM EQMOVPROD MP, EQTIPOMOV TM

INNER JOIN EQPRODUTO eq on mp.codprod = eq.codprod

LEFT JOIN cpcompra cp on mp.codcompra = cp.codcompra

LEFT JOIN cpforneced forn on cp.codfor =forn.codfor

where MP.CODEMP=TM.CODEMP AND MP.CODFILIAL=TM.CODFILIAL AND

MP.CODTIPOMOV=TM.CODTIPOMOV AND tipoprod='P' and mp.codvenda is null and
mp.codcompra is not null



GROUP BY

MP.DTMOVPROD,

MP.CODTIPOMOV,

MP.CODMOVPROD,

MP.CODPROD,

MP.CODNAT,

MP.DOCMOVPROD,

MP.CODCOMPRA,

MP.CODVENDA,

EQ.DESCPROD,

EQ.TIPOPROD,

MP.CODEMP,

MP.CODFILIAL,

TM.CODEMP,

TM.CODFILIAL,

MP.QTDMOVPROD,

MP.SLDMOVPROD,

EQ.CODFISC,

EQ.CUSTOINFOPROD,

FORN.RAZFOR



UNION ALL

SELECT

MP.CODEMP,

MP.CODFILIAL,

MP.DTMOVPROD,

MP.CODMOVPROD,

MP.CODPROD,

MP.CODTIPOMOV,

MP.QTDMOVPROD,

MP.SLDMOVPROD,

MP.CODNAT,

MP.CODCOMPRA,

MP.CODVENDA,

EQ.DESCPROD,

EQ.CODFISC,

MP.DOCMOVPROD,

EQ.TIPOPROD,

CAST((MP.sldmovprod*EQ.CUSTOINFOPROD) AS NUMERIC(15,2)) CUSTO,

TM.CODEMP AS TMEMP,

TM.CODFILIAL AS TMFIL,

cast(CLI.NOMECLI as char(60)) NOMECLIENTE

FROM EQMOVPROD MP, EQTIPOMOV TM

LEFT JOIN EQPRODUTO eq on mp.codprod = eq.codprod

LEFT join vdvenda vd on mp.codvenda=vd.codvenda

LEFT join vdcliente cli on vd.codcli = cli.codcli

where MP.CODEMP=TM.CODEMP AND MP.CODFILIAL=TM.CODFILIAL AND

MP.CODTIPOMOV=TM.CODTIPOMOV AND tipoprod='P' and mp.codcompra is null and
mp.codvenda is not null



GROUP BY

MP.DTMOVPROD,

MP.CODTIPOMOV,

MP.CODMOVPROD,

MP.CODPROD,

MP.CODNAT,

MP.DOCMOVPROD,

MP.CODCOMPRA,

MP.CODVENDA,

EQ.DESCPROD,

EQ.TIPOPROD,

MP.CODEMP,

MP.CODFILIAL,

TM.CODEMP,

TM.CODFILIAL,

MP.QTDMOVPROD,

MP.SLDMOVPROD,

EQ.CODFISC,

EQ.CUSTOINFOPROD,

CLI.NOMECLI

;





---
Este email foi escaneado pelo Avast antivírus.
https://www.avast.com/antivirus


[Non-text portions of this message have been removed]

[setysvar [e-mail] [firebird-support]]

It's a bad idea to mix SQL-89 (joining through using commas) and SQL-92
(using join) syntax, and I'm not used to using GROUP BY on fields used
in a calculation (although this may well be legal). And why do you use
LEFT JOIN TM when you refer to it in the WHERE clause as if it was an
INNER JOIN? Moreover, I do not understand why you use GROUP BY at all in
your query since you do not use any aggregated functions - using SELECT
DISTINCT should be a simpler way to achieve the same result.

Here's an attempt to rewrite your view, it may differ slightly from your
original query since it will not return duplicates if two of the UNION
ALLs in your original query could be identical:

CREATE OR ALTER VIEW ESPELHO12(
CODEMPRESA, CODFILIAL, DTMOVPROD, CODMOVIMENTO, CODPRODUTO,
TIPOMOVIMENTO, QUANTIDADE, SALDO, CFOP, CODCOMPRA,
CODVENDA, DESCRICAO, NCM, DOCUMENTO, TIPOPRODUTO,
CUSTO, TMEMPRESA, TMFILIAL, NOMERAZAOTIPO )
AS
SELECT MP.CODEMP, MP.CODFILIAL, MP.DTMOVPROD, MP.CODMOVPROD,
MP.CODPROD,
MP.CODTIPOMOV, MP.QTDMOVPROD, MP.SLDMOVPROD, MP.CODNAT,
MP.CODCOMPRA,
MP.CODVENDA, EQ.DESCPROD, EQ.CODFISC, MP.DOCMOVPROD,
EQ.TIPOPROD,
CAST((MP.sldmovprod*EQ.CUSTOINFOPROD) AS NUMERIC(15,2)) CUSTO,
TM.CODEMP AS TMEMP, TM.CODFILIAL AS TMFIL, TM.DESCTIPOMOV
FROM EQMOVPROD MP
INNER JOIN EQPRODUTO eq on mp.codprod = eq.codprod
INNER JOIN eqtipomov tm on tm.codtipomov = mp.codtipomov
AND TM.CODEMP = MP.CODEMP
AND TM.CODFILIAL = MP.CODFILIAL
AND TM.CODTIPOMOV = MP.CODTIPOMOV
where EQ.tipoprod='P'
and mp.codvenda is null
and mp.codcompra is null
UNION
SELECT MP.CODEMP, MP.CODFILIAL, MP.DTMOVPROD, MP.CODMOVPROD,
MP.CODPROD,
MP.CODTIPOMOV, MP.QTDMOVPROD, MP.SLDMOVPROD, MP.CODNAT,
MP.CODCOMPRA,
MP.CODVENDA, EQ.DESCPROD, EQ.CODFISC, MP.DOCMOVPROD,
EQ.TIPOPROD,
CAST((MP.sldmovprod*EQ.CUSTOINFOPROD) AS NUMERIC(15,2)) CUSTO,
TM.CODEMP AS TMEMP, TM.CODFILIAL AS TMFIL, FORN.RAZFOR AS NOMEFORNECEDOR
FROM EQMOVPROD MP
INNER JOIN EQTIPOMOV TM ON MP.CODEMP=TM.CODEMP
AND MP.CODFILIAL=TM.CODFILIAL
AND MP.CODTIPOMOV=TM.CODTIPOMOV
INNER JOIN EQPRODUTO eq on mp.codprod = eq.codprod
LEFT JOIN cpcompra cp on mp.codcompra = cp.codcompra
LEFT JOIN cpforneced forn on cp.codfor =forn.codfor
where EQ.tipoprod='P'
and mp.codvenda is null
and mp.codcompra is not null
UNION
SELECT MP.CODEMP, MP.CODFILIAL, MP.DTMOVPROD, MP.CODMOVPROD,
MP.CODPROD,
MP.CODTIPOMOV, mP.QTDMOVPROD, MP.SLDMOVPROD, MP.CODNAT,
MP.CODCOMPRA,
MP.CODVENDA, EQ.DESCPROD, EQ.CODFISC, MP.DOCMOVPROD,
EQ.TIPOPROD,
CAST((MP.sldmovprod*EQ.CUSTOINFOPROD) AS NUMERIC(15,2)),
TM.CODEMP, TM.CODFILIAL, cast(CLI.NOMECLI as char(60))
FROM EQMOVPROD MP
INNER JOIN EQTIPOMOV TM ON MP.CODEMP=TM.CODEMP
AND MP.CODFILIAL=TM.CODFILIAL
AND MP.CODTIPOMOV=TM.CODTIPOMOV
INNER JOIN EQPRODUTO eq on mp.codprod = eq.codprod
LEFT join vdvenda vd on mp.codvenda=vd.codvenda
LEFT join vdcliente cli on vd.codcli = cli.codcli
where EQ.tipoprod='P'
and mp.codcompra is null
and mp.codvenda is not null

HTH,
Set

['Israel Pinheiro' [e-mail] [firebird-support]]

Ok, I understood the settings, but it still does not work in FB 1.5



De: firebird-***@yahoogroups.com [mailto:firebird-***@yahoogroups.com]
Enviada em: segunda-feira, 28 de novembro de 2016 20:21
Para: firebird-***@yahoogroups.com
Assunto: Re: [firebird-support] VIEW CREATES ON FB 2.5 BUT DO NOT CREATE ON FB 1.5





It's a bad idea to mix SQL-89 (joining through using commas) and SQL-92
(using join) syntax, and I'm not used to using GROUP BY on fields used
in a calculation (although this may well be legal). And why do you use
LEFT JOIN TM when you refer to it in the WHERE clause as if it was an
INNER JOIN? Moreover, I do not understand why you use GROUP BY at all in
your query since you do not use any aggregated functions - using SELECT
DISTINCT should be a simpler way to achieve the same result.

Here's an attempt to rewrite your view, it may differ slightly from your
original query since it will not return duplicates if two of the UNION
ALLs in your original query could be identical:

CREATE OR ALTER VIEW ESPELHO12(
CODEMPRESA, CODFILIAL, DTMOVPROD, CODMOVIMENTO, CODPRODUTO,
TIPOMOVIMENTO, QUANTIDADE, SALDO, CFOP, CODCOMPRA,
CODVENDA, DESCRICAO, NCM, DOCUMENTO, TIPOPRODUTO,
CUSTO, TMEMPRESA, TMFILIAL, NOMERAZAOTIPO )
AS
SELECT MP.CODEMP, MP.CODFILIAL, MP.DTMOVPROD, MP.CODMOVPROD,
MP.CODPROD,
MP.CODTIPOMOV, MP.QTDMOVPROD, MP.SLDMOVPROD, MP.CODNAT,
MP.CODCOMPRA,
MP.CODVENDA, EQ.DESCPROD, EQ.CODFISC, MP.DOCMOVPROD,
EQ.TIPOPROD,
CAST((MP.sldmovprod*EQ.CUSTOINFOPROD) AS NUMERIC(15,2)) CUSTO,
TM.CODEMP AS TMEMP, TM.CODFILIAL AS TMFIL, TM.DESCTIPOMOV
FROM EQMOVPROD MP
INNER JOIN EQPRODUTO eq on mp.codprod = eq.codprod
INNER JOIN eqtipomov tm on tm.codtipomov = mp.codtipomov
AND TM.CODEMP = MP.CODEMP
AND TM.CODFILIAL = MP.CODFILIAL
AND TM.CODTIPOMOV = MP.CODTIPOMOV
where EQ.tipoprod='P'
and mp.codvenda is null
and mp.codcompra is null
UNION
SELECT MP.CODEMP, MP.CODFILIAL, MP.DTMOVPROD, MP.CODMOVPROD,
MP.CODPROD,
MP.CODTIPOMOV, MP.QTDMOVPROD, MP.SLDMOVPROD, MP.CODNAT,
MP.CODCOMPRA,
MP.CODVENDA, EQ.DESCPROD, EQ.CODFISC, MP.DOCMOVPROD,
EQ.TIPOPROD,
CAST((MP.sldmovprod*EQ.CUSTOINFOPROD) AS NUMERIC(15,2)) CUSTO,
TM.CODEMP AS TMEMP, TM.CODFILIAL AS TMFIL, FORN.RAZFOR AS NOMEFORNECEDOR
FROM EQMOVPROD MP
INNER JOIN EQTIPOMOV TM ON MP.CODEMP=TM.CODEMP
AND MP.CODFILIAL=TM.CODFILIAL
AND MP.CODTIPOMOV=TM.CODTIPOMOV
INNER JOIN EQPRODUTO eq on mp.codprod = eq.codprod
LEFT JOIN cpcompra cp on mp.codcompra = cp.codcompra
LEFT JOIN cpforneced forn on cp.codfor =forn.codfor
where EQ.tipoprod='P'
and mp.codvenda is null
and mp.codcompra is not null
UNION
SELECT MP.CODEMP, MP.CODFILIAL, MP.DTMOVPROD, MP.CODMOVPROD,
MP.CODPROD,
MP.CODTIPOMOV, mP.QTDMOVPROD, MP.SLDMOVPROD, MP.CODNAT,
MP.CODCOMPRA,
MP.CODVENDA, EQ.DESCPROD, EQ.CODFISC, MP.DOCMOVPROD,
EQ.TIPOPROD,
CAST((MP.sldmovprod*EQ.CUSTOINFOPROD) AS NUMERIC(15,2)),
TM.CODEMP, TM.CODFILIAL, cast(CLI.NOMECLI as char(60))
FROM EQMOVPROD MP
INNER JOIN EQTIPOMOV TM ON MP.CODEMP=TM.CODEMP
AND MP.CODFILIAL=TM.CODFILIAL
AND MP.CODTIPOMOV=TM.CODTIPOMOV
INNER JOIN EQPRODUTO eq on mp.codprod = eq.codprod
LEFT join vdvenda vd on mp.codvenda=vd.codvenda
LEFT join vdcliente cli on vd.codcli = cli.codcli
where EQ.tipoprod='P'
and mp.codcompra is null
and mp.codvenda is not null

HTH,
Set





---
Este email foi escaneado pelo Avast antivírus.
https://www.avast.com/antivirus

['Israel Pinheiro' [e-mail] [firebird-support]]

Last row error



Invalid Token. Dynamic SQL Error. Invalid Command Data Type unknown

De: firebird-***@yahoogroups.com [mailto:firebird-***@yahoogroups.com]
Enviada em: segunda-feira, 28 de novembro de 2016 20:21
Para: firebird-***@yahoogroups.com
Assunto: Re: [firebird-support] VIEW CREATES ON FB 2.5 BUT DO NOT CREATE ON FB 1.5





It's a bad idea to mix SQL-89 (joining through using commas) and SQL-92
(using join) syntax, and I'm not used to using GROUP BY on fields used
in a calculation (although this may well be legal). And why do you use
LEFT JOIN TM when you refer to it in the WHERE clause as if it was an
INNER JOIN? Moreover, I do not understand why you use GROUP BY at all in
your query since you do not use any aggregated functions - using SELECT
DISTINCT should be a simpler way to achieve the same result.

Here's an attempt to rewrite your view, it may differ slightly from your
original query since it will not return duplicates if two of the UNION
ALLs in your original query could be identical:

CREATE OR ALTER VIEW ESPELHO12(
CODEMPRESA, CODFILIAL, DTMOVPROD, CODMOVIMENTO, CODPRODUTO,
TIPOMOVIMENTO, QUANTIDADE, SALDO, CFOP, CODCOMPRA,
CODVENDA, DESCRICAO, NCM, DOCUMENTO, TIPOPRODUTO,
CUSTO, TMEMPRESA, TMFILIAL, NOMERAZAOTIPO )
AS
SELECT MP.CODEMP, MP.CODFILIAL, MP.DTMOVPROD, MP.CODMOVPROD,
MP.CODPROD,
MP.CODTIPOMOV, MP.QTDMOVPROD, MP.SLDMOVPROD, MP.CODNAT,
MP.CODCOMPRA,
MP.CODVENDA, EQ.DESCPROD, EQ.CODFISC, MP.DOCMOVPROD,
EQ.TIPOPROD,
CAST((MP.sldmovprod*EQ.CUSTOINFOPROD) AS NUMERIC(15,2)) CUSTO,
TM.CODEMP AS TMEMP, TM.CODFILIAL AS TMFIL, TM.DESCTIPOMOV
FROM EQMOVPROD MP
INNER JOIN EQPRODUTO eq on mp.codprod = eq.codprod
INNER JOIN eqtipomov tm on tm.codtipomov = mp.codtipomov
AND TM.CODEMP = MP.CODEMP
AND TM.CODFILIAL = MP.CODFILIAL
AND TM.CODTIPOMOV = MP.CODTIPOMOV
where EQ.tipoprod='P'
and mp.codvenda is null
and mp.codcompra is null
UNION
SELECT MP.CODEMP, MP.CODFILIAL, MP.DTMOVPROD, MP.CODMOVPROD,
MP.CODPROD,
MP.CODTIPOMOV, MP.QTDMOVPROD, MP.SLDMOVPROD, MP.CODNAT,
MP.CODCOMPRA,
MP.CODVENDA, EQ.DESCPROD, EQ.CODFISC, MP.DOCMOVPROD,
EQ.TIPOPROD,
CAST((MP.sldmovprod*EQ.CUSTOINFOPROD) AS NUMERIC(15,2)) CUSTO,
TM.CODEMP AS TMEMP, TM.CODFILIAL AS TMFIL, FORN.RAZFOR AS NOMEFORNECEDOR
FROM EQMOVPROD MP
INNER JOIN EQTIPOMOV TM ON MP.CODEMP=TM.CODEMP
AND MP.CODFILIAL=TM.CODFILIAL
AND MP.CODTIPOMOV=TM.CODTIPOMOV
INNER JOIN EQPRODUTO eq on mp.codprod = eq.codprod
LEFT JOIN cpcompra cp on mp.codcompra = cp.codcompra
LEFT JOIN cpforneced forn on cp.codfor =forn.codfor
where EQ.tipoprod='P'
and mp.codvenda is null
and mp.codcompra is not null
UNION
SELECT MP.CODEMP, MP.CODFILIAL, MP.DTMOVPROD, MP.CODMOVPROD,
MP.CODPROD,
MP.CODTIPOMOV, mP.QTDMOVPROD, MP.SLDMOVPROD, MP.CODNAT,
MP.CODCOMPRA,
MP.CODVENDA, EQ.DESCPROD, EQ.CODFISC, MP.DOCMOVPROD,
EQ.TIPOPROD,
CAST((MP.sldmovprod*EQ.CUSTOINFOPROD) AS NUMERIC(15,2)),
TM.CODEMP, TM.CODFILIAL, cast(CLI.NOMECLI as char(60))
FROM EQMOVPROD MP
INNER JOIN EQTIPOMOV TM ON MP.CODEMP=TM.CODEMP
AND MP.CODFILIAL=TM.CODFILIAL
AND MP.CODTIPOMOV=TM.CODTIPOMOV
INNER JOIN EQPRODUTO eq on mp.codprod = eq.codprod
LEFT join vdvenda vd on mp.codvenda=vd.codvenda
LEFT join vdcliente cli on vd.codcli = cli.codcli
where EQ.tipoprod='P'
and mp.codcompra is null
and mp.codvenda is not null

HTH,
Set





---
Este email foi escaneado pelo Avast antivírus.
https://www.avast.com/antivirus

['Leyne, Sean' [e-mail] [firebird-support]]

Post by 'Israel Pinheiro' ***@gmail.com [firebird-support]
Last row error
Invalid Token. Dynamic SQL Error. Invalid Command Data Type unknown

Try testing each SQL individually and then building up the SQL using different UNION combinations to see where the problem is.


Sean

[Dmitry Yemanov [e-mail] [firebird-support]]

Post by 'Israel Pinheiro' ***@gmail.com [firebird-support]
Invalid Token. Dynamic SQL Error. Invalid Command Data Type unknown

Check datatypes of all union parts, i.e.

select A1, B1, C1
union
select A2, B2, C2
union
select A3, B3, C3

Data types for (A1, A2, A3) should match exactly. The same for B and C.


Dmitry