--- In firebird-support@yahoogroups.com, I wrote:

> CREATE PROCEDURE CUSTOMERLATESTMAXPRICE
> (CUSTOMER_ID INTEGER)
> RETURNS
> (PRODUCT_ID INTEGER,
> MAX_DATE DATE,
> MAX_PRICE DECIMAL(9,2))
> AS BEGIN
> FOR SELECT P.PRODUCT_ID, MAX(O.ORDER_DATE)
> FROM PRODUCTS P
> JOIN ORDER_ITEMS I
> ON P.PRODUCT_ID = I.PRODUCT_ID
> JOIN ORDERS O
> ON I.ORDER_ID = O.ORDER_ID
> WHERE
> O.CUSTOMER_ID = :CUSTOMER_ID
> GROUP BY P.PRODUCT_ID
> INTO :PRODUCT_ID, :MAX_DATE
> DO
> BEGIN
> FOR (SELECT MAX(I.PRICE)
> FROM ORDER_ITEMS I
> JOIN ORDERS O
> ON I.ORDER_ID = O.ORDER_ID
> WHERE P.PRODUCT_ID = :PRODUCT_ID
> AND O.CUSTOMER_ID = :CUSTOMER_ID
> AND O.ORDER_DATE = :MAX_DATE
> INTO :MAX_PRICE
> DO
> BEGIN
> SUSPEND;
> END
> END

Arrgh, that should, of course, be

CREATE PROCEDURE CUSTOMERLATESTMAXPRICE
(CUSTOMER_ID INTEGER)
RETURNS
(PRODUCT_ID INTEGER,
MAX_DATE DATE,
MAX_PRICE DECIMAL(9,2))
AS BEGIN

FOR SELECT P.PRODUCT_ID, MAX(O.ORDER_DATE)
FROM PRODUCTS P
JOIN ORDER_ITEMS I
ON P.PRODUCT_ID = I.PRODUCT_ID
JOIN ORDERS O
ON I.ORDER_ID = O.ORDER_ID
WHERE
O.CUSTOMER_ID = :CUSTOMER_ID
GROUP BY P.PRODUCT_ID
INTO :PRODUCT_ID, :MAX_DATE

DO
BEGIN

FOR SELECT MAX(I.PRICE)
FROM PRODUCTS P
JOIN ORDER_ITEMS I
ON P.PRODUCT_ID = I.PRODUCT_ID
JOIN ORDERS O
ON I.ORDER_ID = O.ORDER_ID
WHERE P.PRODUCT_ID = :PRODUCT_ID
AND O.CUSTOMER_ID = :CUSTOMER_ID
AND O.ORDER_DATE = :MAX_DATE
INTO :MAX_PRICE

DO
BEGIN
SUSPEND;
END
END

I think the stored procedure is easier to read than the subselect.

Set