Fernando,


"Fernando Buitrago (Listas y Foros)" schrieb:

>
> Hi
>
> If I try to capture the last record, what is missing?
>
> CREATE PROCEDURE SP_PRODUCT_PRECE (
> P_ESTATION INTEGER,
> P_PRODUCT CHAR (4))
> RETURNS (
> P_PRICE FLOAT)
> AS
> BEGIN
> SELECT PRECIO_COMPRA
> FROM PRODUCTS_PRICES
> WHERE ID_ESTACION = :P_ESTACION AND
> ID_PRODUCT = :P_PRODUCT
> ORDER BY DATE_OF_CHANGE,
> TIME_OF_CHANGE
> INTO :P_PRICE
> END

1.
Why do you want to select all records to get the last one if you're also
using an ORDER BY????
In that case it's easier to change the ordering and select the first
record:

SELECT FIRST PRECIO_COMPRA
FROM PRODUCTS_PRICES
WHERE ID_ESTACION = :P_ESTACION AND
ID_PRODUCT = :P_PRODUCT
ORDER BY DATE_OF_CHANGE DESC,
TIME_OF_CHANGE DESC

If you need that quite often you could think about adding a (combined)
DESC index on the fields DATE_OF_CHANGE, TIME_OF_CHANGE to make it go
lighting fast ;-)



2.

> This Select returns multiple records, But I want just the last.

This select returns no records at all. If you want to return multiple
records from a stored proc you need SUSPEND;

So the syntax should be:

FOR SELECT PRECIO_COMPRA
FROM PRODUCTS_PRICES
WHERE ID_ESTACION = :P_ESTACION AND
ID_PRODUCT = :P_PRODUCT
ORDER BY DATE_OF_CHANGE,
TIME_OF_CHANGE
INTO :P_PRICE

DO BEGIN
/* DO STH. */
/* NOTE! with FB <1.5 you need to have a statement within
the BEGIN ... END, (empty blocks not supported)
END

SUSPEND;

(but usually the SUSPEND is within the DO BEGIN .. END block...)


Regards
Luc.