Hi Andrew !

andrew_s_vaz wrote:

>Hi all,
>
>I'm porting an app from DbIsam and I'm getting this error from one of
>the queries:
>
>************************************
>
>Unsuccessful execution caused by system error that does not preclude
>successful execution of subsequent statements.
>Integer overflow. The result of an integer operation caused the most
>significant bit of the result to carry.
>
>*************************************
>
>The query:
>
>*************************************
>
>Select F.IdFilial, F.FilNomeFan, c.ConvDesc, A.AtnDtAtend, A.IdAtend,
> A.IdConv, x.AtxTotconv, x.AtxTotPart, x.AtxTotMat, x.AtxTotServ,
>x.AtxTotEx, x.AtxSubTot, x.AtxDesconto, x.AtxArred, x.AtxTotGer,
>x.AtxTotPag, x.AtxSaldo,
>count (i.IdItReq) as TotExm,
>sum ((((( i.ItrHon * i.ItrMultHon * i.ItrVlrIndxFat) *
>(i.ItrPorcCob/100)) * (1-(i.ItrDescontoTab/100)) )* i.ItrMultTab)) as
>VlrFat,
>('2005.02.01') as DataIni, ('2005.02.28') as DataFim,
>a.PrcDesc
>
>>From Atendim A
> INNER JOIN Filial f ON (F.IdFilial=A.IdFilialAtn)
> INNER JOIN convenio c on (c.IdConv = A.IdConv)
> INNER JOIN AtendCx x on (x.IdAtend = a.IdAtend) and
> (x.IdFilialAtn = a.IdFilialAtn) and
> (x.AtnAno = a.AtnAno)
> INNER JOIN ItReq i on (i.IdAtend = A.IdAtend) and
> (i.IdFilialAtn = a.IdFilialAtn) and
> (i.AtnAno = a.AtnAno) and
> ((i.ItrFFaturado = 1) or (i.ItrVlrBalc >
>0))
>where
>(A.AtnDtAtend between '2005.02.01 00:00:00' and '2005.02.28
>23:59:59') and
>(a.Idproced = 75)
>Group by F.IdFilial, F.FilNomeFan, c.ConvDesc, A.AtnDtAtend,
>A.IdAtend, A.IdConv, x.AtxTotconv, x.AtxTotPart, x.AtxTotMat,
>x.AtxTotServ, x.AtxTotEx, x.AtxSubTot, x.AtxDesconto, x.AtxArred,
>x.AtxTotGer, x.AtxTotPag, x.AtxSaldo, a.PrcDesc
>Order By 2 ASC, 3 ASC
>
>**********************
>
>The plan:
>
>PLAN SORT (SORT (JOIN (A INDEX (IXATNIDPROCED,IXATNDTATENDASC),X INDEX
>(PK_ATENDCX),I INDEX (IXITREMS,IXITRFFATURADO,IXITRVLRBALC),F INDEX
>(PK_FILIAL),C INDEX (PK_CONVENIO))))
>
>*********************
>
>I'm using Win2k station, Win2k Server, Firebird 1.5.2
>
>
>Seems that I'm doing some illegal math here, but I can't find out
>what. It is working on the older app.
>Any clue of why this is going wrong?
>
>
>
>Thanks
>Andrew
>
>

I think the problem lies here:

sum ((((( i.ItrHon * i.ItrMultHon * i.ItrVlrIndxFat) *
(i.ItrPorcCob/100)) * (1-(i.ItrDescontoTab/100)) )* i.ItrMultTab)) as
VlrFat,

What the type of the involved fields ?

If they are numeric(x,y) (or decimal) you are getting problem because the lot of multiplications and divisions, that is leading to an overflow on the decimals...

Try casting those columns to double precision.

see you !


--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.8.1 - Release Date: 23/03/2005