Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: Why Doesn't SQL This Expression Work?
Date: Mon, 22 Aug 2005 16:42:02 -0400
Message-ID: <F18A6F7CF1661F46920F2CF713122FED46CC72@mail.aveo.aveopharma.com>
Thread-Topic: [SQL] Why Doesn't SQL This Expression Work?
Thread-Index: AcWnVstPNcIoJLYwS5CHzEqRaT48iQAAtFdw
From: "Dmitri Bichko" <dbichko@aveopharma.com>
To: "Lane Van Ingen" <lvaningen@esncc.com>,
	<pgsql-sql@postgresql.org>

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=3D> select (589824 / ((240 * 255840) / 8) * 100);
 ?column?
----------
        0
(1 row)

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

Dmitri

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org=20
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Lane Van Ingen
> Sent: Monday, August 22, 2005 3:51 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Why Doesn't SQL This Expression Work?
>=20
>=20
> Hi, am trying to do a simple computation on two views, but=20
> for some reason the current_util_in computation always=20
> returns zero. All fields being used are integer.
>=20
>   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=20
> current_util_in,
>     from if_history_view1 a, speed_history_view1 b
>     where a.if_id =3D b.if_id
>     and a.if_id =3D 2;
>=20
> The inner computation (a.time_incr * b.speed / 8) evaluated=20
> properly to 7675200. Add the "in_count divide operation", and=20
> the result is zero.
>=20
> The  result expected is a percentage, and should compute to=20
> 7.68 (8 as an integer), when multiplied by 100. What is wrong=20
> here? Here is the result:
>   Row  if_id in_count  time_incr  speed    possible_bytes =20
> current_util_in
>    1    2     589824    240        255840   7675200         0
>=20
>=20
>=20
> ---------------------------(end of=20
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>=20
The information transmitted is intended only for the person or entity to =
which it is addressed and may contain confidential and/or privileged mate=
rial. Any review, retransmission, dissemination or other use of, or takin=
g 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 comput=
er