At 10:49 AM 22/03/2005 +0000, Nick Upson wrote:

>Here is the source
>
>CREATE PROCEDURE BEST_MATCOST (MB_U INTEGER,
> REQD_QTY DECIMAL(6,3))
>returns (RES_PRICE FLOAT,
> RES_QTY FLOAT,
> RES_COST FLOAT)
>AS
> declare variable price1 Float;
> declare variable qty1 Decimal(6,3);
>begin
>/* calculate the best price for this qty of this material */
>
>/* calculate price for exact qty or more & get qty for later */
>SELECT FIRST 1 price, from_qty
>FROM matprice
>WHERE matbrand_u = :mb_u AND :reqd_qty >= from_qty
>ORDER BY from_qty desc
>INTO :price1, :qty1;
>
>res_qty = reqd_qty;
>if (price1 is null) then /* error, no price available */
>begin
> res_price = -1.0;
> res_cost = -1.0;
> exit;
>end
>
>res_price = price1;
>res_cost = reqd_qty * price1;
>
>price1 = 0.0; /* only changes if select sucseeds */
>/* calculate price & min order qty for the next qty */
>SELECT first 1 price, from_qty
>FROM matprice
>WHERE matbrand_u = :mb_u AND :qty1 < from_qty
>ORDER BY from_qty asc
>INTO :price1, :qty1;
>
>/* determine cheapest price */
>if (price1 > 0.0 and (price1 * qty1) < res_cost) then
>begin
> res_price = price1;
> res_cost = qty1 * price1;
> res_qty = qty1;
>end
>end

Is this the same problem Martijn posted or are you hijacking his thread
with a different problem of your own?

If it *is* the same problem, what do you suppose is going to happen to
those return values, if you try to execute this procedure in a script?

./heLen