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();