Thread: or function

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

--- "A. R. Van Hook" <hook@lake-lotawana.mo.us> wrote:

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

try "select sum( (case when i.count=0 then 1 else i.count end) * s.cost
) as COST ...

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

"A. R. Van Hook" <hook@lake-lotawana.mo.us> writes:
> 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

Bitwise OR will surely not do what you want.  I think the most effective
solution is probably CASE:

select sum(case when i.count = 0 then s.cost else i.count * s.cost end) ...
        regards, tom lane