Stored Procedure Issue

UDA
Fan

Stored Procedure Issue

Hi,

I wrote the Stored Procedure below but when I call it, its getting error: CALL Failed. 7631:  MULTILVLBOM_COSTROLLUP_CURSOR:Fetch/Close attempted on a closed cursor in the stored procedure (MULTILVLBOM_COSTROLLUP_CURSOR).

REPLACE PROCEDURE MACH1_PROCS.MULTILVLBOM_COSTROLLUP_CURSOR

( IN IN_USER VARCHAR(50))

BEGIN

DECLARE PLANT VARCHAR(20);

DECLARE PART VARCHAR(50);

DECLARE PROJECT_ID VARCHAR(20);

DECLARE COST_TYPE VARCHAR(5);

DECLARE LEVELS VARCHAR(50);

DECLARE REPORTING_CURRENCY VARCHAR(50);

DECLARE INCUR CURSOR FOR

SELECT A.CMPNT_IDNT_NUM,

A.CMPNT_LVL_ITM_NUM,

A.PRJ_ID,

A.COST_TYP_CD,

1 AS LEVELS,

A.PRJ_DFLT_CCY_CD

FROM MACH1_BVAL.BOM_COST_DATA A

INNER JOIN

(SELECT MAX(PRJ_ID) AS PRJ_ID FROM MACH1_BVAL.EPC_PRJ) B

ON A.PRJ_ID = B.PRJ_ID

GROUP BY 1,2,3,4,5,6;

OPEN INCUR;

START_LABEL: LOOP

FETCH INCUR INTO PLANT,

PART,

PROJECT_ID,

COST_TYPE,

LEVELS,

REPORTING_CURRENCY;

IF SQLSTATE = '00000'

THEN

CALL MACH1_PROCS.MULTILVLBOM_COSTROLLUP_SUMRY(

PLANT,

PART,

PROJECT_ID,

COST_TYPE,

LEVELS,

REPORTING_CURRENCY,

IN_USER);

ELSE

LEAVE START_LABEL ;

END IF;

END LOOP START_LABEL;

CLOSE INCUR;

END;

Any idea why its failing?

3 REPLIES
Teradata Employee

Re: Stored Procedure Issue

Hi,

Can you try re-structuring it to following format:

FOR CurrentRow AS SourceCursor CURSOR FOR    

SELECT

COL1,COL2

FROM Table1

DO

Call SP1(CurrentRow.COL1, CurrentRow.COL2);

END FOR;

HTH!

Regards, Adeel

Fan

Re: Stored Procedure Issue

Hi,

I changed the Procedure as follows but now get this error:

CALL Failed. 7628:  MULTILVLBOM_COSTROLLUP_SUMRY:Invalid statement specified inside a FOR cursor statement.

Any ideas what could be wrong or how to get this to work? Thanks

REPLACE PROCEDURE MACH1_PROCS.MULTILVLBOM_COSTROLLUP_CURSORT

( IN IN_USER VARCHAR(50))

BEGIN

DECLARE PLANT VARCHAR(20);

DECLARE PART VARCHAR(50);

DECLARE PROJECT_ID VARCHAR(20);

DECLARE COST_TYPE VARCHAR(5);

DECLARE LEVELS VARCHAR(50);

DECLARE REPORTING_CURRENCY VARCHAR(50);

FOR CurrentRow AS SourceCursor CURSOR FOR

SELECT A.CMPNT_IDNT_NUM PLANT,

A.CMPNT_LVL_ITM_NUM PART,

A.PRJ_ID PROJECT_ID,

A.COST_TYP_CD COST_TYP,

'1' AS LEVELS,

A.PRJ_DFLT_CCY_CD REPORTING_CURRENCY

FROM MACH1_BVAL.BOM_COST_DATA A

INNER JOIN

(SELECT MAX(PRJ_ID) AS PRJ_ID FROM MACH1_BVAL.EPC_PRJ) B

ON A.PRJ_ID = B.PRJ_ID

GROUP BY 1,2,3,4,5,6

DO

CALL MACH1_PROCS.MULTILVLBOM_COSTROLLUP_SUMRY(

CurrentRow.PLANT,

CurrentRow.PART,

CurrentRow.PROJECT_ID,

CurrentRow.COST_TYP,

CurrentRow.LEVELS,

CurrentRow.REPORTING_CURRENCY,

:IN_USER);

END FOR;

END;

Teradata Employee

Re: Stored Procedure Issue

It should work fine, i tired with dummy definition of tables and SP you are calling in a loop.

Can you share DDLs for tables and SPs for further analysis?

Regards, Adeel