Hi,

> Several billion electronic calculators have been produced which state
> that 1/3 = 0.33333... and not 0, and this is what most people
> understand. SQL is not the private province of developers - my own
> users are invited to write their own queries for their reports - so
> user-friendliness is important.

Do you compare a calculator with SQL expressions ? Ooops ?
Which datatypes contains your calculator ?
When you allow users to write there own queries you asked them to know
SQL-language.
And when you read the SQL-language you should know why (1 / 3) is different
from (1.00 / 3.00).

> Now, I know we are all very intelligent people, and perhaps we find
> it strange that anyone should understand 1/3 to mean "one third". But
> we cannot change the fact that this is exactly what most people do
> understand. So if 1/3 is to mean zero, then SQL calculations become
> highly accident-prone, and from most people's point of view the
> answers given by SQL can be WRONG.

The point of view from the user that uses SQL is WRONG you mean.

> We are in a situation in which we have to explain to our users that
>
> SELECT ANNUAL_OVERHEAD / 12 AS MONTH_OVERHEAD FROM OVERHEADS...
> is not the same as
> SELECT (1/12) * ANNUAL_OVERHEAD AS MONTH_OVERHEAD FROM OVERHEADS...
>
> The second expression returns zero, but anyone with any common sense
> would see that "1/12" means "one twelfth", and should give the RIGHT
> answer, and not zero.
>
> And then, if we explain that
>
> SELECT (1.00/12) * ANNUAL_OVERHEAD
> is different to
> SELECT (1.00/12.00) * ANNUAL_OVERHEAD
> then our users will look at us very strangely, and make a mental note
> to check carefully any calculations we present them with in future!
>
> If there is no problem here, why do we bother to differentiate
> between "/" and "div" in other languages?
>
> I would hazard a guess that quite a lot of group members have had to
> go back to check their code as a result of reading this
> correspondence - because this behaviour is NOT INTUITIVE.
>
> It looks to me as though Oracle have got it right. I am told that in
> Oracle, all the above expressions will return the correct answer.

I can't play with Oracle but all other SQL engines i know (such as MSSQL)
they react on the same way as Firebird and Interbase so as the standard tell
you.

Regards,
Arno