At 12:03 AM 29/07/2003 +0000, you wrote:
I wrote:

> > > I can't reproduce this in an ib_grid. Can you provide an example
> > of the > > SQL involved and also a) what mask you have tried for its
> > DisplayFormat > > property and b) what control you are displaying it in?

You wrote

> > The sql query is:
> >
> > SELECT SUM(U.PAYTOTAL) AS TOTAL FROM MBUSERPAYMENT U
> > LEFT JOIN MBPAYMENT P ON P.PAYMENTKEY=U.PAYMENTKEY
> > LEFT JOIN MBPAYMENTDATA D ON D.PAYMENTKEY=P.PAYMENTKEY
> > WHERE U.USERKEY=:ukey AND D.PROVIDERITEMKEY=:pkey AND P.STATUS=1
> > AND D.PAYFORYEAR=2003 AND D.PAYFORMONTH=5
> >
> > The values that are summed are four -0.01's. The masks don't
>matter
> > as the '.-4' result is the raw result. I think it may be the joins
> > that are confusing it somehow as tests without the joins show
> > correct results. Although it should only be concerned with data
> > values while summing, not how it gets them. It can't be the
> > variables as I've tested it with real values substituted. I'm
> > stumped, maybe you can make some sense of it.
> > You help is appreciated.
> >
> > anth
>
>.....Sorry, the field is displayed in a FastReport report so I
>initially thought it was that, but on further testing it displays in
>TIB_Edit and TIB_Grid...

Anth,
What is the point of the left joins? Not that I can think why the left
joins per se would give you a weird mask in the output. In a proper join
(without the aggregate) they should give you NULL for the left member
where you don't have a match in the other tables.

Guessing that you have 1:1 relationships between these three tables, you
have some kind of a cross-join going on here. Because of the aggregate on
the left, I don't think you can get a reasonable result from this query
without grouping, something like:

SELECT
u.userkey,
SUM(U.PAYTOTAL) AS TOTAL
FROM MBUSERPAYMENT U
LEFT JOIN MBPAYMENT P ON P.PAYMENTKEY=U.PAYMENTKEY
LEFT JOIN MBPAYMENTDATA D ON D.PAYMENTKEY=P.PAYMENTKEY

where D.PROVIDERITEMKEY=:pkey
AND P.STATUS=1
AND D.PAYFORYEAR=2003 AND
D.PAYFORMONTH=5

group by u.userkey
having U.USERKEY=:ukey

That's about all I can come up with...try it and see whether it works.

Helen