On Thursday 2002-02-14 at 19:43:11 +0100, guido.klapperich@... wrote:

> >
> > What happens if you use:
> >
> > SELECT
> > to1valuens DM,
> > (to1valuens / CAST(1.95583 AS NUMERIC(18,6))) EURO
> > FROM
> > Turnover_CS_MP_Month
> > WHERE
> > to1csid = 6 and to1paid = 1 and to1mpid = 2
> >
>
> DM EURO
>
> 149.115,080000 76.241,329768
> -77.374,130000 52,251530
> 80.532,730000 41.175,731020
> 205.586,430000 105.114,672543
> 284.087,030000 145.251,391992
> 711.952,750000 364.015,660870
> 212.314,730000 108.554,797707
> 576.549,930000 294.785,298313
> 404.336,820000 206.734,133335
> 327.176,500000 167.282,688168
> 288.400,760000 147.456,967119
> -190.479,560000 38,503490
>
> > and:
> >
> > SELECT
> > to1valuens DM,
> > CAST((to1valuens / CAST(1.95583 AS NUMERIC(18,6))) AS
> > NUMERIC(18,5)) EURO
> > FROM
> > Turnover_CS_MP_Month
> > WHERE
> > to1csid = 6 and to1paid = 1 and to1mpid = 2
> >
>
> The same as above.
> Sorry, I can't understand, what is happening here. I can't imagine, that IB can't
> hangle negative values.

And it seems to only occur with a scale higher than 4, from what I
read in another post. Have to say it looks like a bug in InterBase's
numeric handling. I assume that if you cast everything as
NUMERIC(18,4), then everything works properly? i.e.:

SELECT
to1valuens DM,
CAST((CAST(to1valuens AS NUMERIC(18,4)) / CAST(1.95583 AS NUMERIC(18,4))) AS
NUMERIC(18,4)) EURO
FROM
Turnover_CS_MP_Month
WHERE
to1csid = 6 and to1paid = 1 and to1mpid = 2


David Trudgett