Please try this and see if this works or not -
-- Create a temporary table to store the recursive results
CREATE OR REPLACE TEMPORARY VIEW recursive_results AS
SELECT p.position_no,
p.position_function,
p.work_function,
p.job_profile_id,
p.pos_cat AS position_category,
p.pos_cat_desc,
p.job_posting_title AS pos_title,
p.employee_status AS emp_status,
p.emp_status_desc,
p.clevel,
p.fund_code,
p.manager_position_id,
p.executive_supervisor,
CAST(p.position_no AS STRING) AS rep_path,
CAST(p.fund_code AS STRING) AS doa_path,
99 AS lvl,
CAST(p.manager_position_id AS STRING) AS root_parent,
CAST(NULL AS STRING) AS prior_non_nafund_code,
CAST(NULL AS STRING) AS prior_mgr_pos_id
FROM TABLE p
WHERE current_date BETWEEN p.position_effective_date AND p.pos_end_date
AND p.IUD_FLAG IN ('I', 'U');
-- Iterate the self-join until no more results are found
CREATE OR REPLACE TEMPORARY VIEW recursive_temp AS
SELECT *
FROM recursive_results;
CREATE OR REPLACE TEMPORARY VIEW recursive_temp_new AS
SELECT p.position_no,
p.position_function,
p.work_function,
p.job_profile_id,
p.pos_cat AS position_category,
p.pos_cat_desc,
p.job_posting_title AS pos_title,
p.employee_status AS emp_status,
p.emp_status_desc,
p.clevel,
p.fund_code,
p.manager_position_id,
p.executive_supervisor,
CONCAT(r.rep_path, '<-', CAST(p.position_no AS STRING)) AS rep_path,
CONCAT(r.doa_path, '<-', CAST(p.fund_code AS STRING)) AS doa_path,
99 AS lvl,
r.root_parent,
r.fund_code AS prior_non_nafund_code,
r.manager_position_id AS prior_mgr_pos_id
FROM TABLE p
JOIN recursive_temp r ON r.position_no = p.manager_position_id;
-- Repeat the self-join until no more results are found
WHILE (SELECT COUNT(*) FROM recursive_temp_new) > 0 DO
INSERT INTO TABLE recursive_temp SELECT * FROM recursive_temp_new;
TRUNCATE TABLE recursive_temp_new;
INSERT INTO TABLE recursive_temp_new
SELECT p.position_no,
p.position_function,
p.work_function,
p.job_profile_id,
p.pos_cat AS position_category,
p.pos_cat_desc,
p.job_posting_title AS pos_title,
p.employee_status AS emp_status,
p.emp_status_desc,
p.clevel,
p.fund_code,
p.manager_position_id,
p.executive_supervisor,
CONCAT(r.rep_path, '<-', CAST(p.position_no AS STRING)),
CONCAT(r.doa_path, '<-', CAST(p.fund_code AS STRING)),
99 AS lvl,
r.root_parent,
r.fund_code AS prior_non_nafund_code,
r.manager_position_id AS prior_mgr_pos_id
FROM TABLE p
JOIN recursive_temp r ON r.position_no = p.manager_position_id;
END WHILE;
-- Final query to retrieve the results
SELECT position_no,
position_function,
work_function,
job_profile_id,
position_category AS pos_cat,
pos_cat_desc,
pos_title AS job_posting_title,
emp_status,
emp_status_desc,
clevel,
doa_path,
rep_path,
root_parent,
prior_non_nafund_code,
prior_mgr_pos_id,
lvl,
manager_position_id,
fund_code,
executive_supervisor
FROM recursive_temp
WHERE prior_non_nafund_code IS NULL;
The alternative approach uses temporary views to simulate the recursive behavior. It iteratively performs self-joins and inserts the results into temporary tables until no more results are found. Finally, the desired result is fetched from the temporary table.
