Skip to Content

HANA Procedure: SAP DBTech JDBC: [1287]: identifier must be declared

I have checked similar questions and couldn't find the answer.

I have used cursors many times before but not with an intersection query, so I think the error is thrown from the first Declare cursor statement. When i call the below procedure, i get error: SAP DBTech JDBC: [1287]: identifier must be declared: PRI

PROCEDURE "SP_RetiredEmp_AddDayCal" ( ) 
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER 
	--DEFAULT SCHEMA <default_schema_name>
	--READS SQL DATA 
	AS
BEGIN
/***************************** 
	Write your procedure logic 
 *****************************/
 DECLARE PRI_T VARCHAR(9);
 DECLARE RECDATE_T DATE;
 DECLARE CHANGEDT_T DATE;
 DECLARE DAYS_T INTEGER :=0;
 
DECLARE CURSOR DISTINCT_PRI for select distinct(PRI) as PRI from (
select distinct(PRI) from "App_RecordDate"
INTERSECT
select distinct(PRI) from "Mobility_ChangeDate");
 
	For D_PRI as DISTINCT_PRI do
             DECLARE CURSOR RECDATE for select RECDATE from "App_RecordDate"  where PRI = D_PRI.PRI order by RECDATE;
		For R_D as RECDATE do
			RECDATE_T := R_D.RECDATE;
			select MAX("CHANGEDT") into CHANGEDT_T  from "Mobility_ChangeDate" where PRI = D_PRI.PRI and CHANGEDT<iterator_row.R_D.RECDATE;
			IF :RECDATE_T < :CHANGEDT_T
				THEN
				DAYS_T := days_between(:RECDATE_T,:CHANGEDT_T);
			ELSE
				DAYS_T := 0;
			END IF;
			Insert into "RetiredEmp_AddDayCal" values (D_PRI.PRI,:RECDATE_T,:CHANGEDT_T,:DAYS_T);
		 


		END FOR;
	END FOR;
END;
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Feb 06, 2017 at 10:26 AM

    You have to transfer the "PRI" value as parameter to the second cursor instead of using it directly.

    E.g.

    ...
    FOR d_pri AS distinct_pri DO
      DECLARE CURSOR recdate(p_pri nvarchar(100)) 
        FOR SELECT recdate FROM "App_RecordDate" WHERE pri= :p_pri ORDER BY recdate;
    
        FOR r_d AS recdate(:d_pri.pri) DO
    ...

    Please adjust the type of the PRI value for the PRI cursor parameter.

    Regards,
    Florian

    Add comment
    10|10000 characters needed characters exceeded