I am new to Snowflake (but with a lot of SQL experience in SQL Server and BigQuery), and I am trying to create a While loop script.
The script raises an error that I can't fix because when I check the official documentation everything looks legit syntax-wise. here is the error I get and the script.
At first, I just generate sample data and create the tables, the loop itself starts on row number 98.
If someone could help me It would be great, thanks a lot.
The error :
Syntax error: unexpected '('. (line 102)
Syntax error line 7 at position 8 unexpected 'SELECT'. (line 102)
I have created a database named temp_db and inserted the data into temp tables.
The code:
CREATE OR REPLACE TEMPORARY TABLE temp_db.public.tbl_workers
AS
SELECT worker_id, worker_name, worker_dob, worker_title, department, manager, Salary
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)
) AS t (worker_id, worker_name, worker_dob, worker_title, department, manager, Salary)
;
-- check the data
SELECT *
FROM temp_db.public.tbl_workers;
-- create the target table and insert the first level into it
CREATE OR REPLACE TEMPORARY TABLE temp_db.public.tbl_workers_rc_results_op2
(
worker_id string,
worker_name string,
worker_title string,
department string,
manager string,
manager_name string,
manager_title string,
org_lvl int
);
INSERT INTO temp_db.public.tbl_workers_rc_results_op2
SELECT worker_id, worker_name, worker_title, department, manager
, CAST(NULL AS string) AS manager_name
, CAST(NULL AS string) AS manager_title, 1 AS org_lvl
FROM temp_db.public.tbl_workers
WHERE manager IS NULL;
-- the loop
BEGIN
LET lvl := 1;
LET flag := FALSE;
WHILE (1 = 1) DO
SELECT TRUE INTO :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;
IF (flag = FALSE) THEN
BREAK OUTER;
END IF;
INSERT 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_level = :lvl;
lvl := lvl 1;
flag = False;
END WHILE;
END;
CodePudding user response：
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.
CodePudding user response：
Observations in your code :
- You are using org_level column name wrong during second insert stmt and using org_lvl for first time for table tbl_workers_rc_results_op2. 2.BREAK OUTER keyword works for loop construct but not for while loop.
- Your loop never exits because of flagging variable is always TRUE Below is code which works well and you can fine tune your logic in case .Please add "execute immediate $$" start of code below code and end with it "$$" to make below code work and change database name to validate.
begin
let lvl := 1;
let flag := FALSE;
loop
SELECT TRUE INTO :flag
FROM LIBRARY_CARD_CATALOG.PUBLIC.TBL_WORKERS_RC_RESULTS_OP2 m JOIN LIBRARY_CARD_CATALOG.PUBLIC.TBL_WORKERS e ON m.worker_id = e.manager
WHERE m.org_lvl = :lvl limit 1;
INSERT INTO
LIBRARY_CARD_CATALOG.PUBLIC.TBL_WORKERS_RC_RESULTS_OP2(
worker_id,
worker_name,
worker_title,
department,
manager,
manager_name,
manager_title,
org_lvl
)
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
LIBRARY_CARD_CATALOG.PUBLIC.TBL_WORKERS_RC_RESULTS_OP2 m
JOIN LIBRARY_CARD_CATALOG.PUBLIC.TBL_WORKERS e ON m.worker_id = e.manager
WHERE
m.org_lvl = :lvl;
IF (flag = TRUE) THEN
break;
END IF;
lvl := lvl 1;
flag := FALSE;
end loop;
end;