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

6 REPLIES 6

Kaniz
Community Manager
Community Manager

Hi @lakshmi s​, To convert the provided code into Databricks SQL, you can use the following equivalent query:

WITH RECURSIVE cte AS (
  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,
    CAST(fund_code AS STRING) AS doa_path,
    CAST(position_no AS STRING) AS rep_path,
    manager_position_id AS root_parent,
    NULL AS PRIOR_NON_NAFUND_CODE,
    NULL AS PRIOR_MGR_POS_ID,
    99 AS lvl,
    manager_position_id,
    fund_code,
    executive_supervisor
  FROM TABLE
  WHERE CURRENT_DATE BETWEEN position_effective_date AND pos_end_date
    AND IUD_FLAG IN ('I', 'U')
  UNION ALL
  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,
    CONCAT(cte.doa_path, '<-', CAST(p.fund_code AS STRING)) AS doa_path,
    CONCAT(cte.rep_path, '<-', CAST(p.position_no AS STRING)) AS rep_path,
    cte.root_parent,
    cte.fund_code AS PRIOR_NON_NAFUND_CODE,
    cte.manager_position_id AS PRIOR_MGR_POS_ID,
    cte.lvl + 1 AS lvl,
    p.manager_position_id,
    p.fund_code,
    p.executive_supervisor
  FROM TABLE p
  JOIN cte ON PRIOR position_no = manager_position_id
)
SELECT
  position_no,
  position_function,
  work_function,
  job_profile_id,
  position_category,
  pos_cat_desc,
  pos_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 cte
WHERE PRIOR fund_code IS NULL
CONNECT BY NOCYCLE PRIOR position_no = manager_position_id

Replace "TABLE" with the actual table name or table alias in your Databricks environment. Also, note that Databricks SQL supports recursive queries using the WITH RECURSIVE clause is similar to the WITH RECURSIVE syntax used in other database systems.

Please adjust the code according to your specific table and column names, and test it in your Databricks SQL environment to ensure it produces the desired results.

lsun6234
New Contributor II

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?

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!