topic Re: help me in converting the below code equivalent to databricks sql PS. with recursive is not w in Data Governance

help me in converting the below code equivalent to databricks sql PS. with recursive is not working

lsun6234 — Thu, 22 Jun 2023 09:41:56 GMT

SELECT position_no,

position_function,

work_function,

job_profile_id,

pos_cat as position_category,

pos_cat_desc,

job_posting_title as pos_title,

employee_status as emp_status,

emp_status_desc,

clevel,

substr(sys_connect_by_path(fund_code, '<-'), 3) doa_path,

substr(sys_connect_by_path(position_no, '<-'), 3) rep_path,

connect_by_root(manager_position_id) root_parent,

PRIOR fund_code as PRIOR_NON_NAFUND_CODE,

PRIOR manager_position_id as PRIOR_MGR_POS_ID,

99 as lvl,

manager_position_id,

fund_code,

executive_supervisor

FROM (select p.position_no, p.manager_position_id, p.fund_code, p.position_function, p.work_function,

p.job_profile_id, p.pos_cat, p.pos_cat_desc, p.job_posting_title, p.employee_status, p.emp_status_desc,

p.clevel,p.executive_supervisor from TABLE p

where current_date between p.position_effective_date and p.pos_end_date

and p.IUD_FLAG IN ('I','U')) p

WHERE PRIOR fund_code IS NULL

CONNECT BY NOCYCLE PRIOR position_no = manager_position_id

Re: help me in converting the below code equivalent to databricks sql PS. with recursive is not working

Anonymous — Fri, 23 Jun 2023 05:17:13 GMT

Hi @lakshmi s

Hope everything is going great.

Just wanted to check in if you were able to resolve your issue. If yes, would you be happy to mark an answer as best so that other members can find the solution more quickly? If not, please tell us so we can help you.

Cheers!

Re: help me in converting the below code equivalent to databricks sql PS. with recursive is not working

lsun6234 — Fri, 23 Jun 2023 06:42:26 GMT

with recursive is not working as said in the description

Re: help me in converting the below code equivalent to databricks sql PS. with recursive is not working

lsun6234 — Fri, 23 Jun 2023 06:44:20 GMT

the solution is not working

Re: help me in converting the below code equivalent to databricks sql PS. with recursive is not w

Anonymous — Wed, 05 Jul 2023 05:54:07 GMT

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.

Re: help me in converting the below code equivalent to databricks sql PS. with recursive is not w

DBSolution — Wed, 31 Jan 2024 00:38:45 GMT

Do we have the solution of this now?