EDB 2.04 B4

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?

SELECT WorkDate,
SUM(CASE
  WHEN EXTRACT(DAYOFWEEK FROM WorkDate)=1 THEN HOURS
  ELSE 0
END) AS Mon,
[...]
SUM(CASE
  WHEN EXTRACT(DAYOFWEEK FROM WorkDate)=0 THEN HOURS
  ELSE 0
END) AS Sun, SUM(Hours) AS Total
FROM ETime GROUP BY WorkDate

Michael


I'm guessing that its the same problem I've encountered. With some expressions the parser can't figure out what the result set should be and takes a guess. The solution for me was to wrap the expression in a CAST.

Roy Lambert [Team Elevate]

On Tue, 22 Mar 2011 07:43:51 +0000, Roy Lambert
<roy.lambert@skynet.co.uk> wrote:

>Michael
>
>
>I'm guessing that its the same problem I've encountered. With some expressions the parser can't figure out what the result set should be and takes a guess. The solution for me was to wrap the expression in a CAST.

Good guess Roy but unfortunately it didn't work in my CASE.

Michael


Can you post what you attempted?

Roy Lambert [Team Elevate]

Michael


Another little thought went through my mind - change the 0 to 0.0

Roy Lambert [Team Elevate]

On Tue, 22 Mar 2011 16:35:47 +0000, Roy Lambert
<roy.lambert@skynet.co.uk> wrote:

>Michael
>
>
>Another little thought went through my mind - change the 0 to 0.0

Yes that's all I had to do, replace all the 0's with 0.0. Thanks Roy.

> Yes that's all I had to do, replace all the 0's with 0.0. Thanks Roy.

While that is an easy workaround I guess Tim will want to fix this behaviour.

John

John


He may not be able to. What we have is:

if(condition then datatype1 else datatype2)

what's your guess as to the datatype?

Roy Lambert

Roy

> He may not be able to. What we have is:
>
> if(condition then datatype1 else datatype2)
>
> what's your guess as to the datatype?

I think there needs to be some sort of type promotion.

What would you expect if you had an expression like 1 + 2.50 ?

I think Michael also said it was returned as a decimal type, so somthing appears to be a bit screwy.

John

John

>> if(condition then datatype1 else datatype2)
>>
>> what's your guess as to the datatype?
>
>I think there needs to be some sort of type promotion.

Probably but I don't envy whoever has to try and work it out.

>What would you expect if you had an expression like 1 + 2.50 ?

Totally different question - were you ever a politician?

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

Roy Lambert [Team Elevate]