3707 error when using recursive 'with' in stored proc in an attempt to concatenate a field.

[Kenton02]

Hello

Using version 15, I am stuck with 3707 error:

[5526] SPL1027:E(L417), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like an 'END' keyword between ';' and the 'with' keyword.'.

Line 417 is the termination semicolon for a recursive query trying to concatenate a field:

CREATE PROCEDURE MYDB_SProc_Db.GetSomeData 
  ( IN  INPUT_Var1 char(3), 
     INPUT_Var2 char(6), 
     INPUT_Var3 TIMESTAMP(2)  
  )

DYNAMIC RESULT SETS 1

BEGIN  
 create volatile table SEATS
 as 
 (...)
 with data
 on commit preserve rows
 ;
 
 with recursive base ( rownumber
        ,COLUMN_1
        ,COLUMN_2
        ,COLUMN_3
        ,COLUMN_4
        ,COLUMN_5
        ,requested_seats
        ,allocated_Seat
       )
 as
 (select  c.rownumber
   ,c.COLUMN_1
   ,c.COLUMN_2
   ,c.COLUMN_3
   ,c.COLUMN_4
   ,c.COLUMN_5
   ,case when base.requested_seats is null then trim(C.requested_seat)
      else base.requested_seats || ',' || trim(C.requested_seat)
    end
   ,case when base.allocated_seats is null then trim(C.allocated_seat)
      else base.allocated_seats || ',' || trim(C.allocated_seat)
    end
 from seats C
 join base
   on base.COLUMN_1  = c.COLUMN_1
  and base.COLUMN_2  = c.COLUMN_2
  and base.COLUMN_3  = c.COLUMN_3
  and base.COLUMN_4  = c.COLUMN_4
  and base.COLUMN_5  = c.COLUMN_5
  and C.rownumber  = base.rownumber+1
  and requested_seat is not null
 )  
 
 select   COLUMN_1
   ,COLUMN_2
   ,COLUMN_3
   ,COLUMN_4
   ,COLUMN_5
   ,requested_seats
   ,allocated_Seats
 from base
 qualify rank() over (partition by COLUMN_1
      ,COLUMN_2
      ,COLUMN_3
      ,COLUMN_4
      ,COLUMN_5 order by rownumber desc) = 1
 ; -- Row 417

 DECLARE  CUR1 CURSOR WITH RETURN ONLY TO CLIENT FOR
        SELECT 
               ...
        ;
       open CUR1;
END;

Any help would be greatly appreciated

[MaximeV]

If the recursive query is the output you need, you should declare it inside the cursor like this ?

   DECLARE  CUR1 CURSOR WITH RETURN ONLY TO CLIENT FOR
        WITH RECURSIVE...
        ;
       open CUR1;
END;

I don't understand on the first part of your code why the recursive query is hanging around.