Syntax issue in HDBPROCEDURE (Cursor)

[former_member585658]

Hello Friends,

In my free time , i was working on a new HCM(Offline Engine) use case solution, i came across this issue. I hate to use cursor, but in xs environment, i had no other choice but to go for it.Now i stumbled upon the syntax issues. hope you can shed some improvements.

 
PROCEDURE "Facilitator"."IntelligentFacilitator.Root.Procedure::AllocationProcedure" ( )
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   --DEFAULT SCHEMA <default_schema_name>
   --READS SQL DATA 
   AS
BEGIN
   /*************************************
       Write your procedure logic 
   *************************************/
 
   
   Declare Cursor cs1 for 
    select "ScheduleID" from  "Facilitator"."IntelligentFacilitator.Root.DDL::ZFacillitator_Tr.ScheduledOffering" 
    where "revisionDate">=current_date;
    declare vScheduleID nvarchar(256);
    declare vRowCount integer;
    for cursorRow as cs1
    do
    --sql script code within cursor loop
    vScheduleID := cursorRow."ScheduleID";
    declare v_cnt_Crdinators Integer;
    declare v_revision_date Date ;
    declare v_Crdinators Integer;


      select COUNT("Cordinators_ID") into  v_cnt_Crdinators 
         from  "Facilitator"."IntelligentFacilitator.Root.DDL::ZFacilitator_Master.Coordinators" 
            where ("isCurrentlyBooked" != 'N' and "NextAvailableDate" is NUll and CurrentscheduleID != vScheduleID ) or "NextAvailableDate" >= current_date;


                select "revisionDate" into v_revision_date from "Facilitator"."IntelligentFacilitator.Root.DDL::ZFacillitator_Tr.ScheduledOffering" 
                 where scheduleID = vScheduleID;
      
                     Select top 1 "Cordinators_ID" into v_Crdinators
                     from  "Facilitator"."IntelligentFacilitator.Root.DDL::ZFacilitator_Master.Coordinators" 
                     where ("isCurrentlyBooked" != 'N' and "NextAvailableDate" is NUll and CurrentscheduleID != vScheduleID ) or "NextAvailableDate" >= current_date;
    
                   update "Facilitator"."IntelligentFacilitator.Root.DDL::ZFacilitator_Master.Coordinators" 
                   set 
                   "isCurrentlyBooked" = 'Y',
                   "NextAvailableDate" = :v_revision_date,
                   "CurrentscheduleID" = :vScheduleID
                   where 
                   "Cordinators_ID" = :v_Crdinators;
                   
                   --more to come
                   
   END FOR;
   
 
END

Error:--[IntelligentFacilitator.Root.Procedure:AllocationProcedure.hdbprocedure] Syntax error in procedure object: incorrect syntax near "declare": line 22 col 5 (at pos 711)

Br,

Gabriel

[pfefferf · Accepted solution]

Place the declare statements after line vScheduleID := cursorRow."ScheduleID"; before that line within the for loop or outside the for loop. Variables must be defined at the beginning of a block (like the for statement is).

[former_member585658 · Accepted solution]

Thanks a lot ! @Florian Pfeffer