From: Lane Van Ingen <lvaningen@esncc.com>
To: Dmitri Bichko <dbichko@aveopharma.com>
To: pgsql-sql@postgresql.org
Subject: Re: Why Doesn't SQL This Expression Work?
Date: Mon, 22 Aug 2005 16:51:14 -0400
Message-ID: <EKEMKEFLOMKDDLIALABIIEKNCBAA.lvaningen@esncc.com> (raw)
In-Reply-To: <F18A6F7CF1661F46920F2CF713122FED46CC72@mail.aveo.aveopharma.com>
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@aveopharma.com]
Sent: Monday, August 22, 2005 4:42 PM
To: Lane Van Ingen; pgsql-sql@postgresql.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@postgresql.org
> [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?
>
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lvaningen@esncc.com, dbichko@aveopharma.com
Subject: Re: Why Doesn't SQL This Expression Work?
In-Reply-To: <EKEMKEFLOMKDDLIALABIIEKNCBAA.lvaningen@esncc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox