You've reached the limit for recursive calls

Bogdan

-----Original Message-----
From: jjochoa75 [mailto:jochoa@...]
Sent: Monday, March 28, 2005 8:49 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Too many executions of the same request



Firebird 1.5.2 Superserver (Windows)

This statement (and procedure) has been working fine for a while.
Now I find this error in the following statement only for certain ID
(13033).

Select
WORK_CENTER_ID,
WORK_CENTER,
DESCRIPTION,
COST,
sum(HRS_PER_FT) as hrs_per_ft,
sum(TOTAL) as total
from sp_get_assem_routing_summ(13033)
group by
WORK_CENTER_ID,
WORK_CENTER,
DESCRIPTION,
COST

PLAN SORT (JOIN (A INDEX (RDB$FOREIGN3),B INDEX (RDB$PRIMARY16))
(TB_ASSEMBLIES INDEX (IDX_TB_ASSEMBLIES_ITEM_ID))JOIN (A INDEX
(RDB$FOREIGN1),B INDEX (RDB$PRIMARY55))):
PLAN SORT (JOIN (A INDEX (RDB$FOREIGN3),B INDEX (RDB$PRIMARY16))
(TB_ASSEMBLIES INDEX (IDX_TB_ASSEMBLIES_ITEM_ID))JOIN (A INDEX
(RDB$FOREIGN1),B INDEX (RDB$PRIMARY55)))

Adapted plan:
PLAN SORT (JOIN (A INDEX (FK_ASSEMBLIES_ROUTING_MPK_TB_WORK_CENTERS
(RDB$PRIMARY16))(TB_ASSEMBLIES INDEX
(IDX_TB_ASSEMBLIES_ITEM_FK_ASSEMBLIES_BOM_MSTRIPK_TB_ITEMSEIGN1),B
INDEX (RDB$PRIMARY55)))

When I run it I get this error: "Too many executions of the same
request".

The procedure is recursive.

The procedure code is:

CREATE PROCEDURE SP_GET_ASSEM_ROUTING_SUMM (
ASSEMBLY_ID INTEGER)
RETURNS (
WORK_CENTER_ID INTEGER,
WORK_CENTER VARCHAR (6),
DESCRIPTION VARCHAR (25),
HRS_PER_FT DOUBLE PRECISION,
COST DOUBLE PRECISION,
TOTAL DOUBLE PRECISION)
AS
declare variable SUBASSEMBLY_ID INTEGER;
declare variable RAW_TYPE VARCHAR(1);
declare variable item_id integer;
BEGIN
/* Procedure body */
subassembly_id = assembly_id;
FOR SELECT A.HRS_PER_FT, A.work_center_id, WORK_CENTER,
DESCRIPTION, COST
FROM tb_assemblies_routing A LEFT JOIN TB_WORK_CENTERS B
ON A.WORK_CENTER_ID = B.TB_WORK_CENTERS_ID
WHERE A.ASSEMBLY_ID = :ASSEMBLY_ID

INTO :HRS_PER_FT, :work_center_id, :WORK_CENTER, :DESCRIPTION, :COST
DO BEGIN
TOTAL = COST * HRS_PER_FT;
suspend;
END

FOR SELECT A.ITEM_ID, B.RAW_TYPE
FROM tb_assemblies_bom A LEFT JOIN tb_items B
ON A.ITEM_ID = B.TB_ITEMS_ID
WHERE a.ASSEMBLY_ID = :ASSEMBLY_ID
INTO :ITEM_ID, :RAW_TYPE
DO BEGIN
if (RAW_TYPE<>'R') then begin
select tb_assemblies_id from tb_assemblies
where item_id = :item_id
into :subassembly_id;

for select /*subassembly_id,*/ HRS_PER_FT, work_center_id,
WORK_CENTER, DESCRIPTION, COST, TOTAL
from sp_get_assem_routing_summ(:subassembly_id)
into /*:subassembly_id,*/ :HRS_PER_FT, :work_center_id,
:WORK_CENTER, :DESCRIPTION, :COST, :TOTAL
do begin

suspend;
end

end

END
END.



The wierd part is that:
SELECT * FROM SP_GET_ASSEM_ROUTING_SUMM(13033)
works perfectly and returns 2262 records (good time-no problem) with
the plan:

PLAN JOIN (A INDEX (RDB$FOREIGN3),B INDEX (RDB$PRIMARY16))
(TB_ASSEMBLIES INDEX (IDX_TB_ASSEMBLIES_ITEM_ID))JOIN (A INDEX
(RDB$FOREIGN1),B INDEX (RDB$PRIMARY55)):
PLAN JOIN (A INDEX (RDB$FOREIGN3),B INDEX (RDB$PRIMARY16))
(TB_ASSEMBLIES INDEX (IDX_TB_ASSEMBLIES_ITEM_ID))JOIN (A INDEX
(RDB$FOREIGN1),B INDEX (RDB$PRIMARY55))

Adapted plan:
PLAN JOIN (A INDEX (FK_ASSEMBLIES_ROUTING_MPK_TB_WORK_CENTERS
(RDB$PRIMARY16))(TB_ASSEMBLIES INDEX
(IDX_TB_ASSEMBLIES_ITEM_FK_ASSEMBLIES_BOM_MSTRIPK_TB_ITEMSEIGN1),B
INDEX (RDB$PRIMARY55))

and, if I execute:

SELECT * FROM SP_GET_ASSEM_ROUTING_SUMM(13033)
ORDER BY
WORK_CENTER_ID,
WORK_CENTER,
DESCRIPTION,
COST

I R E C E I V E N O R E C O R D S B A C K!

Note: The speed is ok, the proble is the error and the result.

Can someone help me to solve this problem
Thanks

Juan Jose.






Yahoo! Groups Links









--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.8.4 - Release Date: 27.3.2005