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

[prashantsoni]

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;

[pfefferf · Accepted solution]

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