Last row error
Invalid Token. Dynamic SQL Error. Invalid Command Data Type unknown De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Enviada em: segunda-feira, 28 de novembro de 2016 20:21 Para: firebird-support@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
