with
    tree (id,pid) as (
      select ic.ID,ic.PID from test.tbl ic
  ),
    rr (id,pid) as (
    select tr.id, tr.pid from tree tr where tr.pid in (select  id
                                                       from test.tbl2
                                                        where  id=123456 --для одного ключа
    )
    union all
    select tt.id,tt.pid from tree tt, rr
    where tt.pid = rr.id
  )
select rec.id from rr rec, test.tbl i
where rec.id = i.id  order by  i.DATE  desc fetch first 1 row only;

DECLARE GLOBAL TEMPORARY TABLE SESSION.TREE(ID BIGINT, PID BIGINT)
WITH REPLACE
ON COMMIT PRESERVE ROWS NOT LOGGED;

BEGIN
 
  DECLARE SQLCODE INT;
  DECLARE Vrb_Id BIGINT;
  DECLARE TRG_ID INT;

DECLARE Crs_Upd CURSOR WITH HOLD FOR   SELECT DISTINCT  id
                                       from test.tbl2
                                       WITH UR
FOR READ ONLY;
OPEN Crs_Upd;

SET TRG_ID = 0;
FETCH FROM Crs_Upd into Vrb_Id;
WHILE (SQLCODE != 100) DO
  begin 
   
 tree (id,pid) as (
      select ic.ID,ic.PID from test.tbl ic
  ),
    rr (id,pid) as (
    select tr.id, tr.pid from tree tr where tr.pid = Vrb_id --для переменной курсора
    )
    union all
    select tt.id,tt.pid from tree tt, rr
    where tt.pid = rr.id
  )
select rec.id INTO TRG_ID from rr rec, test.tbl i
where rec.id = i.id  order by  i.DATE  desc fetch first 1 row only;

 
    
 INSERT INTO SESSION.TREE( ID, PID) VALUES( TRG_ID, Vrb_Id);
end;

  FETCH FROM Crs_Upd into Vrb_Id;
END WHILE;
CLOSE Crs_Upd;
COMMIT;
END;

java.sql.SQLException: An unexpected token "with
          tree (id,pid) as" was found following "100) DO
    begin

".  Expected tokens may include:  "<psm_if>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.7.85