All Forums Database
Kenton02 1 post Joined 11/15
26 Nov 2015
3707 error when using recursive 'with' in stored proc in an attempt to concatenate a field.

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 19 posts Joined 11/13
27 Nov 2015

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.

You must sign in to leave a comment.