A SQL while loop code in Snowflake does not work

This can be written as a recursive CTE. Albeit not what it turns out this question is wanting.

I have used a CTE for the DATA thus it looks messed up.

but as a full self contained blob:

WITH data AS (
  SELECT *
  FROM 
  VALUES  ('02B06EEE', 'Idona Z. Morrison', '19970908', 'Customer Service Representative', 'Customer Relationship', '53A3EB61', 6523)
    , ('03339B30', 'Angelica Hicks', '19930920', 'Customer Relationship Manager', 'Customer Relationship', 'pCF469A0', 16490)
    , ('0500B400', 'Caesar Q. Walter', '19900214', 'Staffing Coordinator', 'Human Resources', 'EDF53F0C', 11000)
    , ('06669B60', 'Cedric Mckenzie', '19910930', 'Transfers Specialist', 'Back Office', '87264Z22', 6300)
    , ('088D77AA', 'Kuame Ashley', '19831021', 'Receptionist', 'Headquarters', 'tt7E4y55', 6300)
    , ('124DE17F', 'George G. Warner', '19971216', 'Legal Counsel', 'Legal', '62y2yF4F', 22985)
    , ('124yE17F', 'Hop Church', '19630207', 'Chief creative officer', 'Management', '62y2yF4F', 38900)
    , ('182FBE76', 'Karyn Ryan', '19710508', 'Intern', 'Sales', '96EF1725', 7460)
    , ('193B6A1D', 'Kadeem Norman', '19930826', 'Customer Service Representative', 'Customer Relationship', '519211D3', 6405)
    , ('196B6Z1Z', 'Rahim Hess', '19911010', 'Deposits Specialist', 'Back Office', '911E6E0y', 8500)
    , ('275ED6C4', 'Octavius K. Franco', '19831229', 'Customer Service Representative', 'Customer Relationship', '519211D3', 6400)
    , ('279EZ6C4', 'Hedy Odonnell', '19880111', 'Tester', 'QA', '919211Z6', 11040)
    , ('2E618FB3', 'Timothy Dillon', '20001216', 'Customer Service Representative', 'Customer Relationship', '53A3EB61', 6400)
    , ('2E618FB6', 'Dorian Norris', '19910115', 'Deposits Specialist', 'Back Office', '196B6Z1Z', 6000)
    , ('32C2CF4F', 'Kylynn Calderon', '19840730', 'Clerk', 'Headquarters', 'tt7E4y55', 5400)
    , ('33963B93', 'Erica I. Roberson', '19880119', 'Programmer', 'Technology', 'BCF469A0', 22369)
    , ('3DB15457', 'Lydia Hogan', '19590718', 'Sales Agent', 'Sales', '73B70CE5', 22460)
    , ('41666EEF', 'Farrah Rodgers', '19600612', 'Chief operating officer', 'Management', '62y2yF4F', 40100)
    , ('4D2BCBBF', 'Hermione Q. Bowers', '19850804', 'DBA', 'Technology', 'pCF469A0', 23500)
    , ('4D2pCppF', 'Kaseem Saunders', '19891101', 'Commercial Junior', 'Headquarters', 'E56E232C', 8000)
    , ('4Z2BCBBF', 'Jeremy Cote', '19830101', 'Withdraws Specialist', 'Back Office', '9FZC9897', 6901)
    , ('519211D3', 'Kennan Dejesus', '19871009', 'Customer Service Representative', 'Customer Relationship', '03339B30', 10123)
    , ('52864CD0', 'Tyler Murray', '19890103', 'IT Support ', 'Technology', '87264D22', 8500)
    , ('53A3EB61', 'Ronan Hicks', '19940629', 'Customer Service Representative', 'Customer Relationship', '03339B30', 9846)
    , ('58DD1EFF', 'Kevin Mcdowell', '19930826', 'Clerk', 'Headquarters', 'tt7E4y55', 5200)
    , ('58yy1EFF', 'Bruno Hansen', '19760107', 'Chief Technology Officer', 'Management', '62y2yF4F', 49640)
    , ('611673FE', 'Craig T. Sargent', '19860914', 'Receptionist', 'Headquarters', '088D77AA', 5931)
    , ('62y2yF4F', 'Elmo H. Stark', '19660729', 'Chief executive officer', 'Management', NULL, 64020)
    , ('66966B96', 'Randall Houston', '19830919', 'Project Manager', 'Back Office', '911E6E0y', 18500)
    , ('670D11D6', 'Oliver Graham', '19870104', 'Customer Service Representative', 'Customer Relationship', '519211D3', 5986)
    , ('670Z11Z6', 'Rebekah K. Berry', '19881005', 'Deposits Specialist', 'Back Office', '196B6Z1Z', 6000)
    , ('73B70CE5', 'Gisela Z. Mercado', '19891101', 'Sales Manager', 'Sales', '62y2yF4F', 21630)
    , ('73B7PCE9', 'Zigi Zag', '20060407', 'Cyber Specialist', 'Technology', '58yy1EFF', 500)
    , ('870W10DQ', 'Jasmine Cote', '20070122', 'Cyber Specialist', 'Technology', '58yy1EFF', 700)
    , ('87264D22', 'Basil F. Becker', '19900227', 'Controller', 'Finance', 'E56E262y', 21097)
    , ('87264Z22', 'Brent V. Day', '19810313', 'Transfers Specialist', 'Back Office', '911E6E0y', 8704)
    , ('8F182FD6', 'Gregory X. Rhodes', '19860625', 'Customer Service Representative', 'Customer Relationship', '53A3EB61', 6400)
    , ('8F182FZ6', 'Quinn P. Patton', '19830510', 'Tester', 'QA', '919211Z6', 12000)
    , ('911E3E0C', 'Sarah W. Fulton', '19700429', 'Logistics Planner', 'Headquarters', '41666EEF', 12000)
    , ('911E6E0y', 'Quamar L. Rodriquez', '19850813', 'Back Office Manager', 'Back Office', '41666EEF', 20647)
    , ('919211Z6', 'Vaughan Albert', '19891101', 'QA Manager', 'QA', '58yy1EFF', 18463)
    , ('92864CZ0', 'Catherine Banks', '19850418', 'Transfers Specialist', 'Back Office', '87264Z22', 6320)
    , ('937D21AD', 'Zoe G. Silva', '19750429', 'IT Support ', 'Technology', '87264D22', 8500)
    , ('967Z21ZZ', 'Shea R. Cunningham', '19840801', 'Transfers Specialist', 'Back Office', '87264Z22', 6320)
    , ('96EF1725', 'Lila X. Cervantes', '19981007', 'Sales Agent', 'Sales', '73B70CE5', 20600)
    , ('96Z6EB61', 'Hashim Smith', '19900228', 'Programmer', 'Technology', 'BCF469A0', 19880)
    , ('990CBA12', 'Nigel Q. Hawkins', '19710206', 'Accountant', 'Finance', 'B3C14522', 11500)
    , ('9FAC9857', 'Zane Jordan', '19910103', 'Programmer', 'Technology', 'BCF469A0', 20960)
    , ('9FZC9897', 'Jasmine Mullen', '19780105', 'Withdraws Specialist', 'Back Office', '911E6E0y', 8700)
    , ('AA7E4C55', 'Hashim Smith', '19820103', 'Management Analyst', 'Headquarters', '41666EEF', 17000)
    , ('B3C14522', 'Macy P. Ramsey', '19831005', 'Senior Accountant', 'Finance', '87264D22', 15000)
    , ('BCF469A0', 'Norman N. Stuart', '19970216', 'Senior Programmer', 'Technology', '58yy1EFF', 33050)
    , ('BCF469Z0', 'Cally Z. West', '19831011', 'Withdraws Specialist', 'Back Office', '9FZC9897', 6480)
    , ('C418AB87', 'Halee K. Warner', '19980122', 'Job Recruiter', 'Human Resources', 'EDF53F0C', 9500)
    , ('DEC56B53', 'Brody Q. Nielsen', '19810615', 'Accountant', 'Finance', 'B3C14522', 12600)
    , ('E56E232C', 'Vaughan Albert', '19871111', 'Commercial Specialist', 'Headquarters', '124yE17F', 19460)
    , ('E56E262y', 'Maisie N. Strickland', '19620427', 'Chief financial officer', 'Finance', '62y2yF4F', 45200)
    , ('EB5CC983', 'Dennis U. Valdez', '19860615', 'Job Recruiter', 'Human Resources', 'EDF53F0C', 9000)
    , ('EDF53F0C', 'Emerson D. Ramos', '19800204', 'HR Manager', 'Human Resources', '41666EEF', 18000)
    , ('EyF56F0y', 'Alika B. Shields', '19800818', 'Project Manager', 'Back Office', '911E6E0y', 19306)
    , ('FCEFAF9B', 'Gareth Acevedo', '19690313', 'Accountant', 'Finance', '87264D22', 11500)
    , ('pCF469A0', 'Glenna Fernandez', '19841218', 'Chief information officer', 'Management', '58yy1EFF', 40200)
    , ('tt7E4y55', 'Samantha Sykes', '19611014', 'Administrative Manager', 'Headquarters', '41666EEF', 10060)
  t (worker_id, worker_name, worker_dob, worker_title, department, manager, Salary)
)
SELECT * FROM (
    WITH RECURSIVE hi_cte AS (
          SELECT 
                worker_id, 
                worker_name,
                worker_title,
                department,
                manager, 
                NULL::text AS manager_name,
                NULL::text AS manager_title,
                1 AS org_lvl
          FROM data
          WHERE manager IS NULL
      
          UNION ALL
      
          SELECT e.worker_id,
                 e.worker_name, 
                 e.worker_title, 
                 e.department,
                 e.manager,
                 m.worker_name AS manager_name, 
                 m.worker_title AS manager_title,
                 m.org_lvl + 1 AS org_lvl
            FROM hi_cte m 
            JOIN data e 
                ON m.worker_id = e.manager
    )
    select * from hi_cte
)
;

gives (trimmed):

WORKER_ID WORKER_NAME WORKER_TITLE DEPARTMENT MANAGER MANAGER_NAME MANAGER_TITLE ORG_LVL
62y2yF4F Elmo H. Stark Chief executive officer Management 1
124DE17F George G. Warner Legal Counsel Legal 62y2yF4F Elmo H. Stark Chief executive officer 2
124yE17F Hop Church Chief creative officer Management 62y2yF4F Elmo H. Stark Chief executive officer 2
41666EEF Farrah Rodgers Chief operating officer Management 62y2yF4F Elmo H. Stark Chief executive officer 2
58yy1EFF Bruno Hansen Chief Technology Officer Management 62y2yF4F Elmo H. Stark Chief executive officer 2
73B70CE5 Gisela Z. Mercado Sales Manager Sales 62y2yF4F Elmo H. Stark Chief executive officer 2
E56E262y Maisie N. Strickland Chief financial officer Finance 62y2yF4F Elmo H. Stark Chief executive officer 2
E56E232C Vaughan Albert Commercial Specialist Headquarters 124yE17F Hop Church Chief creative officer 3
911E3E0C Sarah W. Fulton Logistics Planner Headquarters 41666EEF Farrah Rodgers Chief operating officer 3
911E6E0y Quamar L. Rodriquez Back Office Manager Back Office 41666EEF Farrah Rodgers Chief operating officer 3
AA7E4C55 Hashim Smith Management Analyst Headquarters 41666EEF Farrah Rodgers Chief operating officer 3

but if you have a table, replace data and use:

WITH RECURSIVE hi_cte AS (
      SELECT 
            worker_id, 
            worker_name,
            worker_title,
            department,
            manager, 
            NULL::text AS manager_name,
            NULL::text AS manager_title,
            1 AS org_lvl
      FROM data
      WHERE manager IS NULL
  
      UNION ALL
  
      SELECT e.worker_id,
             e.worker_name, 
             e.worker_title, 
             e.department,
             e.manager,
             m.worker_name AS manager_name, 
             m.worker_title AS manager_title,
             m.org_lvl + 1 AS org_lvl
        FROM hi_cte m 
        JOIN data e 
            ON m.worker_id = e.manager
)
select * from hi_cte

and then you can do inserts, or what ever you please.


Eventually, I used Javascript to make it work:

CREATE OR REPLACE PROCEDURE temp_db.public.p_workers_loop()
RETURNS string
LANGUAGE javascript
AS 
$$  
    var _lvl  = 1;
    var _flag = 1;
    var _query;
    
    while (_flag == 1)
    {
    
        _query = snowflake.createStatement({
            sqlText: "SELECT 1 AS flag FROM temp_db.public.tbl_workers_rc_results_op2 m JOIN temp_db.public.tbl_workers e " +
            "ON m.worker_id = e.manager WHERE m.org_lvl = " + _lvl + " LIMIT 1;" 
        });
                
        _flag = _query.execute().getRowCount();
        
        _query = snowflake.createStatement({
            sqlText: "INSERT INTO temp_db.public.tbl_workers_rc_results_op2 " +
            "SELECT e.worker_id, e.worker_name, e.worker_title, e.department, e.manager, m.worker_name AS manager_name" +
            ", m.worker_title AS manager_title, m.org_lvl + 1 AS org_lvl " +
            "FROM temp_db.public.tbl_workers_rc_results_op2 m JOIN temp_db.public.tbl_workers e " +
            "ON m.worker_id = e.manager " + 
            "WHERE m.org_lvl = " + _lvl + ";"        
        });
                
        _query.execute();
            
        _lvl += 1;        
    }  
$$    
 
CALL temp_db.public.p_workers_loop();