Hi Roger.

Re:

> SELECT ANNUAL_OVERHEAD / 12 AS MONTH_OVERHEAD FROM OVERHEADS...
> is not the same as
> SELECT (1/12) * ANNUAL_OVERHEAD AS MONTH_OVERHEAD FROM OVERHEADS...

These are different even *with* the interpretation of 1/12 as 1.0 / 12.0 because
floating-point calculations are not precise.

It so happenned that one of my staff in 1995 wrote 3 SPs that calculated pays, tax, etc.
in 3 different ways.

For weeks the trial balance was ok. Then it was off by 3 cents. My clients spent weeks of
effort looking for the error(s) that caused it.

It turned out to be the use of floating-point numbers.

x/12.0 is more accurate that (1./12.) * x, so you had better explain to your users this so
that they will always get consistent results and know which one is more accurate.

From a different point of view, anyone who creates a report should check that it produces
correct results with a small sample.

Raymond Kennington

rogervellacott wrote:

>
> 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!

I don't think these are different: 1./12 and 1./12.

...

> 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.

If one allocates 100 pens equally amongst 12 people then each one gets 8 pens; the
remaining 4 can be kept in the box for the next allocation.

Therefore, the correct answer to the question 100/12 is 8 each, not 8.5.

Your use of the word "correct" is to define "correct" to suit your usage, which is a
priori self-referential (i.e. circular) and has no value in a logical argument.

Cheers,
Raymond Kennington