Re: Why Doesn't SQL This Expression Work?

Thanks again, Dmitri. I put a round() function around it, and got exactly
what I was looking for. Tried a cast earlier, but put it in the wrong place!
:-( Just noted that the manual (version 8.0, section 9.3) warns about
integer arithmetic and truncation of results, but fortunately there is a way
around it! Thanks again ....

-----Original Message-----
From: Dmitri Bichko [mailto:dbichko(at)aveopharma(dot)com]
Sent: Monday, August 22, 2005 4:42 PM
To: Lane Van Ingen; pgsql-sql(at)postgresql(dot)org
Subject: RE: [SQL] Why Doesn't SQL This Expression Work?

I believe the problem is that the expression is being eavluated as an
integer, so it's rounded down before it's multiplied by 100;

A simple cast to float4 should help:

test=> select (589824 / ((240 * 255840) / 8) * 100);
?column?
----------
0
(1 row)

test=> select (589824 / ((240 * 255840)::float4 / 8) * 100);
?column?
------------------
7.68480300187617
(1 row)

Dmitri

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Lane Van Ingen
> Sent: Monday, August 22, 2005 3:51 PM
> To: pgsql-sql(at)postgresql(dot)org
> Subject: [SQL] Why Doesn't SQL This Expression Work?
>
>
> Hi, am trying to do a simple computation on two views, but
> for some reason the current_util_in computation always
> returns zero. All fields being used are integer.
>
> select a.if_id,
> a.in_count,
> a.time_incr,
> b.speed,
> ((a.time_incr * b.speed) / 8) as possible_bytes,
> (a.in_count / ((a.time_incr * b.speed) / 8) * 100) AS
> current_util_in,
> from if_history_view1 a, speed_history_view1 b
> where a.if_id = b.if_id
> and a.if_id = 2;
>
> The inner computation (a.time_incr * b.speed / 8) evaluated
> properly to 7675200. Add the "in_count divide operation", and
> the result is zero.
>
> The result expected is a percentage, and should compute to
> 7.68 (8 as an integer), when multiplied by 100. What is wrong
> here? Here is the result:
> Row if_id in_count time_incr speed possible_bytes
> current_util_in
> 1 2 589824 240 255840 7675200 0
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you received
this in error, please contact the sender and delete the material from any
computer