Stored procedure to find record with max values, help!!
Hi,
I have a procedure that will search for a table and find the max value
of a column, then get the record that has this max value.
Because this table is huge, from above, I have to search twice. Is there any
other method That I can only serach once and get the record?
The table LotJob in the following SPL is the huge table.
Table Lot has 6,000 rows.
Table LotJob has 30,000,000 rows.
Table Route has 5,000 rows
Thanks in advance.
Jack
>>>>>>>>>>>>> SPL codes >>>>>>>>>>
CREATE PROCEDURE lott02a(
,i_Lot CHAR(16) -- Lot
) RETURNING
,INTEGER -- Instance
,CHAR(16) -- Lot
,CHAR(16) -- Product
,CHAR(16) -- Process
,CHAR(16) -- Route
,CHAR(32) -- RouteName
;
DEFINE o_Instance INTEGER;
DEFINE o_Lot CHAR(16);
DEFINE o_Product CHAR(16);
DEFINE o_Process CHAR(16);
DEFINE o_Route CHAR(16);
DEFINE o_RouteName CHAR(32);
DEFINE w_Process CHAR(16);
DEFINE w_LotJobSequence INTEGER;
DEFINE ErrorNumber INTEGER;
DEFINE ErrorSQL INTEGER;
DEFINE ErrorISAM INTEGER;
DEFINE ErrorObject CHAR(40);
LET o_Instance = 0;
FOREACH
SELECT Lot
,Product
,Process
INTO o_Lot
,o_Product
,w_Process
FROM Lot
WHERE Lot LIKE i_Lot
SELECT MAX(LotJobSequence)
INTO w_LotJobSequence
FROM LotJob
WHERE Lot = o_Lot;
IF w_LotJobSequence IS NULL
THEN
LET o_Process = w_Process;
LET o_Route = NULL;
LET o_RouteName = NULL;
ELSE
SELECT
Process
,Route
INTO
o_Process
,o_Route
FROM LotJob
WHERE LotJob.Lot = o_Lot
AND LotJob.LotJobSequence = w_LotJobSequence;
SELECT RouteName
INTO o_RouteName
FROM Route
WHERE Route = o_Route;
END IF
LET o_Instance = o_Instance + 1;
RETURN
o_Instance
,o_Lot
,o_Product
,o_Process
,o_Route
,o_RouteName
WITH RESUME;
END FOREACH
END PROCEDURE;