Hi Helen,

Thanks for the response. I was using interbase 6.0 with Firebird 1.0.

When you say where VORP > "" is not legal syntax in Firebird. Do you
mean the "" or the >? Although "" is not a SQL standard, but it works
ok in Firebird 1.0 so it reduce a number of code execution to search
and replace ' to '' from data entry. Do you think we should keep
the "" in our SQL query?

I have fixed the query by: cast((sum(DB_AMOUNT)*100) as integer) -
cast((sum(CR_AMOUNT)*100) as integer) <> 0

cheers,
Duncan

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

> At 12:26 AM 2/08/2004 +0000, you wrote:
> >I have the following queries and results:
> >
> >1) select filenumber, sum(DB_Amount), sum(CR_Amount)
> >from ledger where VORP > "" group by filenumber, VORP
> >having sum(DB_AMOUNT) - sum(CR_AMOUNT) = 0
> >
> >sample results:
> >FILENUMBER SUM SUM1
> >=========== ======== ========
> > 8552 15 15
> > 29450 3756.94 3756.94
> > 29657 920.19 920.19
> > 29863 100 100
> > 29956 19000 19000
> > 29958 1518 1518
> > 29970 100 100
> > 30532 3000 3000
> >
> >2) select filenumber, sum(DB_Amount), sum(CR_Amount)
> >from ledger where VORP > "" group by filenumber, VORP
> >having sum(DB_AMOUNT) - sum(CR_AMOUNT) <> 0
> >
> >sample results:
> >FILENUMBER SUM SUM1
> >=========== ======== ========
> > 26062 2926 4646
> > 29933 7717.48 7717.48
> > 30190 14476.03 14476.03
> > 30473 533.48 533.48
> > 30497 3323.7 3323.7
> > 30525 1184.41 1184.41
> > 30529 1262.88 1262.88
> >
> >Both results were not meeting the expectation. The selection from
> >result 1) does not return filenumber 29933, but the selection from
> >result 2) includes those = 0.
> >
> >Is there a problem in my SQL using the SUM function?
>
> There are potential precision problems in comparisons with zero, if

either

> of those columns is a float or double precision type.
>
> I wonder which DB engine you are using --- IB 5.x?
>
> where VORP > ""
>
> is not legal syntax in Firebird.
>
> The GROUP BY clauses in both of these queries would be disallowed

in

> Firebird 1.5 because VORP is not in the SELECT list.
>
> /heLen