cancel
Showing results for 
Search instead for 
Did you mean: 
Data Governance
Join discussions on data governance practices, compliance, and security within the Databricks Community. Exchange strategies and insights to ensure data integrity and regulatory compliance.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

lsun6234
New Contributor II

 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

5 REPLIES 5

with recursive is not working as said in the description

Anonymous
Not applicable

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. 

Anonymous
Not applicable

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!

lsun6234
New Contributor II

the solution is not working

DBSolution
New Contributor II

Do we have the solution of this now?

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group