Hi David,

Thanks for your help.

Actually, following your idea, the sub-select should be evaluated in the
context of the main select in order to return what I'm expecting. Something
like this :

SELECT P.ID, P.PRODUCTID, P.OUTLETID, P.VALUEDATE, P.SALEPRICE, P.FACING
FROM PRESENCE P
WHERE
(P.OUTLETID IN (112169, 112170, 112171, 112172, 112173, 112174,
112175))
and
(P.STATUS = 2)
and
(P.VALUEDATE =
(SELECT MAX(VALUEDATE)
FROM PRESENCE P2
WHERE
P2.PRODUCTID = P.PRODUCTID <=====
and
(P2.OUTLETID = P.OUTLETID) <=====
and
(P2.VALUEDATE >= '2003-09-01')
and
(P2.VALUEDATE < '2003-11-01')
and
(P2.STATUS = 2))
)
order by P.PRODUCTID, P.OUTLETID, P.VALUEDATE

And as you expected, this is not very efficient. It takes 1.40 min to run
while the following SP that scan the original query and skips unwanted
records runs in 240ms !

CREATE PROCEDURE GET_TEMPLATE_DATA
RETURNS (
ID INTEGER,
OUTLETID INTEGER,
PRODUCTID INTEGER,
SALEPRICE NUMERIC(9,2),
FACING SMALLINT,
VALUEDATE DATE)
AS
DECLARE VARIABLE CURR_OUTLETID INTEGER;
DECLARE VARIABLE CURR_PRODUCTID INTEGER;
BEGIN

CURR_OUTLETID = null;
CURR_PRODUCTID = null;

FOR
SELECT P.ID, P.OUTLETID, P.PRODUCTID, P.SALEPRICE, P.FACING, P.VALUEDATE
FROM PRESENCE P
WHERE
(
(PRESENCE.OUTLETID IN (112169, 112170, 112171, 112172, 112173, 112174,
112175))
and
(PRESENCE.VALUEDATE >= '2003-09-01')
and
(PRESENCE.VALUEDATE < '2003-11-01')
and
(PRESENCE.STATUS = 2)

)
order by P.PRODUCTID, P.OUTLETID, P.VALUEDATE desc

INTO :ID,
:OUTLETID,
:PRODUCTID,
:SALEPRICE,
:FACING,
:VALUEDATE
DO
BEGIN
if ((CURR_OUTLETID is null) or (CURR_PRODUCTID is null) or
(:outletid <> CURR_OUTLETID) or (:PRODUCTID <> CURR_PRODUCTID)
)
then
SUSPEND;

CURR_OUTLETID = :OUTLETID;
CURR_PRODUCTID = :PRODUCTID;
END
END

Best regards.

--
Jerome