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