pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedor function
3+ messages / 3 participants
[nested] [flat]
* or function
@ 2006-12-30 17:43 A. R. Van Hook <hook@lake-lotawana.mo.us>
0 siblings, 2 replies; 3+ messages in thread
From: A. R. Van Hook @ 2006-12-30 17:43 UTC (permalink / raw)
To: 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@lake-lotawana.mo.us
(816) 578-4704 - Home
(816) 578-4215 - City
(816) 564-0769 - Cell
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: or function
@ 2006-12-30 18:10 chester c young <chestercyoung@yahoo.com>
parent: A. R. Van Hook <hook@lake-lotawana.mo.us>
1 sibling, 0 replies; 3+ messages in thread
From: chester c young @ 2006-12-30 18:10 UTC (permalink / raw)
To: A. R. Van Hook <hook@lake-lotawana.mo.us>; +Cc: pgsql-sql
--- "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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: or function
@ 2006-12-30 18:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: A. R. Van Hook <hook@lake-lotawana.mo.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2006-12-30 18:11 UTC (permalink / raw)
To: A. R. Van Hook <hook@lake-lotawana.mo.us>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-12-30 18:11 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-30 17:43 or function A. R. Van Hook <hook@lake-lotawana.mo.us>
2006-12-30 18:10 ` chester c young <chestercyoung@yahoo.com>
2006-12-30 18:11 ` Tom Lane <tgl@sss.pgh.pa.us>
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