--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:

> 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

Hi Helen,
Unfortunately no luck with that or any other variation of SQL I can
think of, the strange thing is that if I run the same SQL in the
IB_SQL console or Interbase PlanAnalyser the correct '-0.04' result
is shown. Also results with 2 decimal places where the 2 digits are
greater than zero, e.g. '-0.17' appears fine as well so its as
though the minus in '.-4' is being put in the zeros place if the
first decimal place is zero.
I'll email you a small sample program if you'd like, but i run bcb6
not delphi.

cheers,
anth