Thanks for your input Svein, it helped to guide me to the answer. What
I've done is to add a field to the Weigh table - ash_rate - which gets
updates at the first stage and then use that value to calculate the
output I want. (And yes there are many suppliers and many different
supplier ash and water payment rates)
This now works every time.
Regards
Alan

begin
update weigh
set
dollar_rate=coalesce(
(select water_rate
from supp_waterrates
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_rate=coalesce(
(select ash_rate
from supp_ashrates
where weigh.supp_code=supp_ashrates.supp_code
and weigh.ash_pc>=supp_ashrates.ash_from
and weigh.ash_pc<supp_ashrates.ash_to
),0),
ash_deduction=coalesce(
(select
round(ceiling(supp_ashrates.ash_from-weigh.ash_pc)*weigh.ash_rate,2)
from supp_ashrates
where weigh.supp_code=supp_ashrates.supp_code
and weigh.ash_pc>=supp_ashrates.ash_from
and weigh.ash_pc<supp_ashrates.ash_to),0),
net_rate=dollar_rate+ash_deduction, /* calculated amount
is a minus */
dollar_total=net_rate*tonnes
where current_YN='Y';
end
--
Alan J Davies
Aldis


Quoting Svein Erling Tysvaer <svein.erling.tysvaer@...>:

> Normally, I would have recommended you to run something like:
>
> select weight.<primary_key_field>
> from weigh
> join supp_ashrates on weigh.supp_code=supp_ashrates.supp_code
> join supplier on <whatever, you lack any link to this table>
> where 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
> group by 1
> having count(*) > 1
>
> to find weight records return more than one value (which you cannot have
> in your update statement).
>
> However, I note two things in your update statement. One of them is
> easily seen when changing from implicit to explicit joins as above - you
> join to supplier without telling how to join to this table. Hence, your
> query will invariably fail if you have more than one supplier!
>
> The other thing I note, is that you mention weight in both UPDATE and
> (sub-SELECT) - something you definitely do not want to do unless you
> qualify them (and I doubt you want to do it at all in your case).
>
> So try something like:
>
> update weigh
> set dollar_rate=coalesce(
> (select water_rate from supp_waterrates
> 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
> JOIN supplier ON ... //delete the entire line unless it matters
> 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';
>
> And don't forget that this updates all rows with weight.current_YN='Y'
> regardless of whether it matches any record in supp_waterrates and
> supp_ashrates.
>
> HTH,
> Set
>
> Alan.Davies@... wrote:
>> 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
>