Yes, it's intentional, because the select have more fields. The complete SQL :


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


--- Original Message -----
From: Svein Erling Tysvær
To: firebird-support@yahoogroups.com
Sent: Tuesday, September 06, 2005 10:25 AM
Subject: [firebird-support] Re: Subselect error


Just another question whilst waiting for Arno: Is it intentional that
your subselect

(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,

SUMs a field in NVV and not in NVV2? A subselect from a table in the
main select rather than the one in the subselect confuses my head
enough to understand that Firebird has problems giving an answer...

Maybe fixing this would be enough for the 'GROUP BY ..., 4' to work?
Set

--- In firebird-support@yahoogroups.com, "Ricardo Malikoski" wrote:
> Oks..
>
> Thanks...
>
> Helppppp.. hehehehe
> ----- Original Message -----
> From: Martijn Tonies
> To: firebird-support@yahoogroups.com
> Sent: Monday, September 05, 2005 3:09 PM
> Subject: Re: [firebird-support] Subselect error
>
>
> Well, it's time to wait for Arno to reply then :-)
>
> With regards,
>
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle &
MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>
> > Firebird version is 1.52.
> >
> >
> > Can you do a GROUP BY ..., Base17
> > R : Don´t work too...
> > ----- Original Message -----
> > From: Martijn Tonies
> > To: firebird-support@yahoogroups.com
> > Sent: Monday, September 05, 2005 11:49 AM
> > Subject: Re: [firebird-support] Subselect error
> >
> >
> >
> >
> > > It´s result in other error : "Cannot use an aggregate
function in a
> GROUP
> > BY clause"
> >
> > Hmm... what version of Firebird?
> >
> > Can you do a GROUP BY ..., Base17




++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




------------------------------------------------------------------------------
YAHOO! GROUPS LINKS

a.. Visit your group "firebird-support" on the web.

b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


------------------------------------------------------------------------------



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