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

Database
Teradata Employee

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

1 REPLY
Teradata Employee

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

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.