Roy

> >What would you expect if you had an expression like 1 + 2.50 ?
>
> Totally different question - were you ever a politician?

That's a bit low don't you think <bg>.

Actually I don't think it's as different as you might think.  Anyway you piqued my curiosity so I gave it a quick whirl.

If you use sum(if(expr intval else floatval))  it always returns a float (regardless of the order of intval and
floatval).
If you use sum(case when expr then intval else floatval end) you get a float.
If you use sum(case when expr then floatval else intval end) you get an integer.

John

John

>That's a bit low don't you think <bg>.

Old Yorkshire saying "if you can't kick a man when he's down when can you kick him"

>Actually I don't think it's as different as you might think. Anyway you piqued my curiosity so I gave it a quick whirl.
>
>If you use sum(if(expr intval else floatval)) it always returns a float (regardless of the order of intval and
>floatval).
>If you use sum(case when expr then intval else floatval end) you get a float.
>If you use sum(case when expr then floatval else intval end) you get an integer.

And in turn you piqued mine. You didn't go far enough

select
sum(case
when 1=3 then 0.7
when 1=1 then 9.9
else 23  end)
from bayesian

gives 1390385.69999853

select
sum(case
when 1=1 then 0.7
--when 1=1 then 9.9
else 23  end)
from bayesian

gives 0

I'm leaving it to Tim!

Roy Lambert

On Wed, 23 Mar 2011 15:35:08 +0000, Roy Lambert
<roy.lambert@skynet.co.uk> wrote:

>>I think Michael also said it was returned as a decimal type, so somthing appears to be a bit screwy.
>
>He'd need to clarify what he actually wrote "Total has decimal values but the day values have the decimal portion truncated." which could mean they were returned as integer or as decimal with 0 after the point, or decimal with only 2 places etc.

The day values are Integers, the total field has 2 decimal places even
in cases where the day and the total values should be identical. The
day values are not rounded up but trucated or floored.

Michael,

<< Hours is a float field. When the query below is run, Total has decimal
values but the day values have the decimal portion truncated. Why is
that? >>

The CASE operator is not performing type promotion, rather it's just picking
the first type and assuming that the rest are the same.  I'll have a fix for
this in the next build.

--
Tim Young
Elevate Software
www.elevatesoft.com