Skip to Content

Syntax issue in HDBPROCEDURE (Cursor)

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Feb 10, 2019 at 08:16 PM

    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).

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.