Lists: | pgsql-sql |
---|
From: | "A(dot) R(dot) Van Hook" <hook(at)lake-lotawana(dot)mo(dot)us> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | or function |
Date: | 2006-12-30 17:43:01 |
Message-ID: | 4596A525.9050008@lake-lotawana.mo.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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(at)lake-lotawana(dot)mo(dot)us
(816) 578-4704 - Home
(816) 578-4215 - City
(816) 564-0769 - Cell
From: | chester c young <chestercyoung(at)yahoo(dot)com> |
---|---|
To: | "A(dot) R(dot) Van Hook" <hook(at)lake-lotawana(dot)mo(dot)us> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: or function |
Date: | 2006-12-30 18:10:38 |
Message-ID: | 20061230181038.48746.qmail@web54313.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
--- "A. R. Van Hook" <hook(at)lake-lotawana(dot)mo(dot)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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "A(dot) R(dot) Van Hook" <hook(at)lake-lotawana(dot)mo(dot)us> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: or function |
Date: | 2006-12-30 18:11:36 |
Message-ID: | 13471.1167502296@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
"A. R. Van Hook" <hook(at)lake-lotawana(dot)mo(dot)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