Subject Re: [ib-support] stored proc question with 2 updates
Author Ann W. Harrison
At 05:13 AM 9/5/2002 -0400, Alan J Davies wrote:
>I have a problem at the moment in updating a single field in a table. This
>is a monetary value based on EITHER the rate per load x # of loads OR the
>rate per tonne x total tonnes.

I'd create two different procedures and invoke one or the other,
depending on what data you've got. Failing that, you ought to put
a conditional expression so you don't do both calculations. What
you've done with the last line of the where clause
(HaulRate.Pay_Per_Load='Y' in the first update statement
and HaulRate.Pay_Per_Load='N' in the second)
won't work. If there are no matches, Haul_Gbp will be set to

Or have I completely misunderstood?

This expression

Select Count((Weigh.Farm_Code)*HaulRate.Gbp_Per_Load)

seems very odd. Why do you want to get the count of Farm_Code
times Gbp_Per_Load? Wouldn't this make more sense?

Select (Count(Weigh.Farm_Code)*HaulRate.Gbp_Per_Load)

That would first get the number of Farm_Codes then multiply
it by the Gbp_Per_Load

Or perhaps I'm confused...


We have answers.