Fw: [firebase-br] Dúvida com SQL

[Ricardo Malikoski]

----- Original Message ----- 
From: Ricardo Malikoski 
To: FireBase 
Sent: Wednesday, September 21, 2005 10:30 AM
Subject: Re: [firebase-br] Dúvida com SQL


Ok amigo, aí vai todo o SQL.

Obs.. ja tentei colocar GROUP BY dentro de cada sub-query e não adiantou(Cannot use an aggregate function in a GROUP BY clause).
         Mudei também uma opção no FireBird.Conf um parâmetro "OldParameterOrdering" que o colega Joel do Prado Júnior indicou, e nada também. Obrigado ao colega Rodolpho da Silva Nascimento, mas também não funcionou.

SELECT NV.OpFis_Codigo, SUM(NV.NF_VlrNota) as totContabil, 
           SUM(NVV.NF_VlrBaseICMS7) as Base7,
           SUM(NVV.NF_VlrBaseICMS12) as Base12, SUM(NVV.NF_VlrBaseICMS17) as Base18,
           SUM(NVV.NF_VlrBaseICMS25) as Base25, SUM(NVV.NF_VlrBaseICMS27) as Base27,
           SUM(NVV.NF_VlrICMS7) as Vlr7,
           SUM(NVV.NF_VlrICMS12) as Vlr12, SUM(NVV.NF_VlrICMS17) as Vlr18,
           SUM(NVV.NF_VlrICMS25) as Vlr25, SUM(NVV.NF_VlrICMS27) as Vlr27,
           (SELECT SUM(NVV.NF_BaseICMSX) FROM NotasVenda_Valores NVV2
              WHERE NVV2.Fil_Codigo = NVV.Fil_Codigo AND NVV2.Ser_Codigo = NVV.Ser_Codigo AND
              NVV2.NF_Numero = NVV.NF_Numero AND NVV2.NF_AliqICMSX = 17 ) AS Base17,
           (SELECT SUM(NVV.NF_BaseICMSX) FROM NotasVenda_Valores NVV2
              WHERE NVV2.Fil_Codigo = NVV.Fil_Codigo AND NVV2.Ser_Codigo = NVV.Ser_Codigo AND
              NVV2.NF_Numero = NVV.NF_Numero AND NVV2.NF_AliqICMSX = 26 ) AS Base26,
           (SELECT SUM(NVV.NF_VlrICMSX) FROM NotasVenda_Valores NVV2
              WHERE NVV2.Fil_Codigo = NVV.Fil_Codigo AND NVV2.Ser_Codigo = NVV.Ser_Codigo AND
              NVV2.NF_Numero = NVV.NF_Numero AND NVV2.NF_AliqICMSX = 17 ) AS Vlr17,
           (SELECT SUM(NVV.NF_VlrICMSX) FROM NotasVenda_Valores NVV2
              WHERE NVV2.Fil_Codigo = NVV.Fil_Codigo AND NVV2.Ser_Codigo = NVV.Ser_Codigo AND
             NVV2.NF_Numero = NVV.NF_Numero AND NVV2.NF_AliqICMSX = 26 ) AS Vlr26,
           SUM(NVV.NF_VlrIsentasICMS) as Isentas,
           SUM(NVV.NF_VlrOutrasICMS ) as Outras, SUM(NVV.NF_VlrBaseIPI) as BaseIPI,
           SUM(NVV.NF_VlrIPI) as VlrIPI, SUM(NVV.NF_BaseICMSFrete) as BaseFrete,
           SUM(NVV.NF_VlrICMSFrete) as VlrICMSFrete, SUM(NV.NF_VlrFrete) as VlrFrete
           FROM ((NotasVenda NV
           INNER JOIN NotasVenda_Valores NVV ON NV.Fil_Codigo = NVV.Fil_Codigo
                                            AND NV.Ser_Codigo = NVV.Ser_Codigo
                                            AND NV.NF_Numero = NVV.NF_Numero)
           LEFT JOIN ParametrosContabeis PC ON NV.OpFis_Codigo = PC.OpFis_Codigo
                                           AND NV.OrFis_Codigo = PC.OrFis_Codigo)
           WHERE NV.NF_Situacao = 'A'
           AND ((NV.OpFis_Codigo BETWEEN 500 AND 999) OR (NV.OpFis_Codigo > 4999))
           
           AND NV.Fil_Codigo = 1
           AND NV.Ser_Codigo IN ('U') AND NV.NF_DataLcto
           BETWEEN '01/01/2005' AND '12/31/2006'
GROUP BY NV.OpFis_Codigo


Ricardo Malikoski


  ----- Original Message ----- 
  From: Augusto 
  To: FireBase 
  Sent: Tuesday, September 20, 2005 6:01 PM
  Subject: Re: [firebase-br] Dúvida com SQL


  Cara, manda o SQL todo.