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.

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.

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.

Roger Vellacott
Passfield Data Systems