Subject Re: multiple row problem in sp update
Author Adam
Hello Alan,

> I get the error "multiple rows in singleton select"

This means that an operation that is expecting to return only a single
record is actually returning two or more.

> begin
> update weigh
> set dollar_rate=coalesce(

OK, from here on you are only allowed to run a query that returns a
single record (because each record in weigh may only have a single
value in the dollar_rate field obviously). My bet is that you have at
least one record in weigh where current_YN='Y' for which the below
query returns multiple records.

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