Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id F06599FA28E for ; Sat, 30 Dec 2006 13:48:10 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 35977-06 for ; Sat, 30 Dec 2006 13:48:06 -0400 (AST) X-Greylist: delayed 00:05:01.15466 by SQLgrey-1.7.4 Received: from rwcrmhc14.comcast.net (rwcrmhc14.comcast.net [204.127.192.84]) by postgresql.org (Postfix) with ESMTP id BEF589FA217 for ; Sat, 30 Dec 2006 13:48:05 -0400 (AST) Received: from [192.168.1.2] (c-69-242-166-153.hsd1.mo.comcast.net[69.242.166.153]) by comcast.net (rwcrmhc14) with ESMTP id <20061230174301m1400h7lp3e>; Sat, 30 Dec 2006 17:43:02 +0000 Message-ID: <4596A525.9050008@lake-lotawana.mo.us> Date: Sat, 30 Dec 2006 11:43:01 -0600 From: "A. R. Van Hook" User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: or function Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50 X-Spam-Level: X-Archive-Number: 200612/194 X-Sequence-Number: 27150 I have been trying to do an 'or' function such that if a field value is zero then use 1 as a multiplier: "select sum((1 | i.count) * s.cost) as COST seems to work ok when the value is 0 but I get the wrong value is i.count in not zero stid | count | tot | ldate | pkcnt | status | cost ------+-------+-----+------------+-------+--------+------ 2995 | 12 | 44 | 12/18/2006 | 32 | Active | 3.60 qs "select sum(i.count * s.cost) from inventory i,stock s, stockkey k where i.stid=s.stid and i.status='Active' and s.ctype = k.cd and k.value = 'Other' and s.ssp = 'Stock'" sum ------- 43.20 qs "select sum((1|i.count) * s.cost) from inventory i,stock s, stockkey k where i.stid=s.stid and i.status='Active' and s.ctype = k.cd and k.value = 'Other' and s.ssp = 'Stock'" sum ------- 46.80 43.20 <> 46.80 Thanks -- Arthur R. Van Hook Mayor The City of Lake Lotawana hook@lake-lotawana.mo.us (816) 578-4704 - Home (816) 578-4215 - City (816) 564-0769 - Cell