Create GTT with Recursive Table

Database
Enthusiast

Create GTT with Recursive Table

Hello everyone, I came here to get some help or ideas for recoding a table and population design. Currently I have a single complex statement that creates and populates a table, and creates a recursive table within, some innner selects and and union all. Below is an example. It gets created in regular perm space. I need to create a GTT and then populate the table there, and maintain the same logic during this conversion. I'm not very familiar with working with recursions. Any and all design approach ideas are welcome. Please let me know what other details you need. Thanks.

CREATE TABLE database_1.table_1, NO FALLBACK AS ( 

WITH RECURSIVE recursive_table

       (g_nbr

      ,h_cd

      ,d_nbr

      ,dbs_cd

      ,dc_cd

      ,lvl) AS

      (SELECT d_nbr

             ,h_cd

             ,d_nbr

             ,dbs_cd

             ,dc_cd

             ,0

       FROM database_1.tblbase_1

       WHERE d_tp = 'P'

       UNION ALL

       SELECT a.g_nbr

             ,a.h_cd

             ,b.d_nbr

             ,b.dbs_cd

             ,b.dc_cd

             ,a.lvl+1

       FROM   recursive_table a

             ,someoutsideview.dc_lk b

       WHERE a.d_nbr = b.pdq_nbr)

       SELECT g_nbr

             ,h_cd

             ,d_nbr

             ,dbs_cd

             ,dc_cd

             ,lvl

       FROM   recursive_table)

WITH DATA

UNIQUE PRIMARY INDEX (g_nbr, d_nbr);