Hi
I have a problem updating a table in an sp.
System is Windows 2003 server 4gb ram, stacks of disk, Firebird 1.54
I get the error "multiple rows in singleton select" when I try to run
this revised sp. I want to add a penalty of $0.10 for each full
percentage point over the ash_base - generally 22% but it varies hence
a table with the different rates.
The tables (cutdown) are
weigh
supp_code ash_pc water_pc
3 24 34
3 21 31

supp_ashrates
supp_code ash_from ash_to ash_base ash_rate
3 0 22 22 0.00
3 22 100 22 0.10

The water_rate part works fine and if I run this as a query on its own
it works fine
select
round(ceiling(supp_ashrates.ash_from-weigh.ash_pc)*supp_ashrates.ash_rate,2)
from supp_ashrates, weigh, supplier
where weigh.supp_code=supp_ashrates.supp_code
and weigh.ash_pc>supp_ashrates.ash_base
and weigh.ash_pc>=supp_ashrates.ash_from
and weigh.ash_pc<=supp_ashrates.ash_to
and weigh.dollar_rate>0


begin
update weigh
set dollar_rate=coalesce(
(select water_rate from supp_waterrates, weigh
where weigh.supp_code=supp_waterrates.supp_code
and weigh.water_pc>=supp_waterrates.water_from
and weigh.water_pc<supp_waterrates.water_to),0),
ash_deduction=coalesce(
(select
round(ceiling(supp_ashrates.ash_from-weigh.ash_pc)*supp_ashrates.ash_rate,2)
from supp_ashrates, weigh, supplier
where weigh.supp_code=supp_ashrates.supp_code
and weigh.ash_pc>supp_ashrates.ash_base
and weigh.ash_pc>=supp_ashrates.ash_from
and weigh.ash_pc<=supp_ashrates.ash_to
and weigh.dollar_rate>0),0),
net_rate=dollar_rate+ash_deduction, /* Ash_deduction is a minus */
dollar_total=tonnes*net_rate
where current_YN='Y';
end

I understand that there is a conflict in trying to update, but can't
get to grips with it. Any advice will be gladly received. If a fuller
description is needed, together with full table data, it can be
uploaded.
Thanks
Alan

--
Alan J Davies
Aldis