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

--
Nick
-----We Solve your Computer Problems---
Founder & Listowner of the Prolifics User Group
Panther, Ingres, UNIX, Interbase, Firebird - Available Shortly


--
Nick
-----We Solve your Computer Problems---
Founder & Listowner of the Prolifics User Group
Panther, Ingres, UNIX, Interbase, Firebird - Available Shortly