Modifying SQL query to Data bricks SQL query

Question

Rewriting Oracle SQL query to Data bricks SQL query. Can anyone please help me on below Oracle SQL query that is compatible in Data bricks.

SELECT DISTINCT 
  STT_O_ID AS ANLY_S_ID,
  OG AS OG_ID 
FROM
  (SELECT DISTINCT 
    M.STT_O_ID,
    M.DEP_U_ID,
    O.OB_OF_ANA_C_ID AS OG 
  FROM
    (SELECT DISTINCT 
      CONNECT_BY_ROOT DEP1.DEP_O_ID STT_O_ID,
      CONNECT_BY_ROOT DEP1.DEP_O_TYP STA_O_TYP,
      DEP1.DEP_U_ID,
      DEP1.DEP_UP_TYP,
      LEVEL L2CHILD,
      (
        SYS_CONNECT_BY_PATH (DEP1.DEP_U_ID, '/')
      ) PRNT2CHILD_PATH 
    FROM
      (SELECT 
        * 
      FROM
        PR.DEPDGDGCY DEP 
      WHERE DEP.DEP_UP_TYP <> 'ALYC_S' 
        AND DEP.E_DT > SYSDATE) DEP1 CONNECT BY NOCYCLE PRIOR DEP1.DEP_U_ID = DEP1.DEP_O_ID 
      AND PRIOR DEP1.DEP_UP_TYP = DEP1.DEP_O_TYP START WITH DEP1.DEP_O_TYP = 'ALYC_S') M 
    LEFT JOIN PR.PLE_NUE_SRC R 
      ON R.PLE_ID = M.DEP_U_ID 
      AND R.E_DT > SYSDATE
    LEFT JOIN PR.REV_SRC_IO RV 
      ON RV.REV_S_ID = R.REV_S_ID 
      AND RV.E_DT > SYSDATE
    LEFT JOIN PR.REV_GOR O 
      ON RV.REV_OIGR_ID = O.REV_OIGR_ID 
  ORDER BY STT_O_ID,
    OG ASC) Y
  LEFT JOIN CRE.O_NMS ORGN 
    ON Y.OG = ORGN.OG_ID 
    AND ORGN.CU_N_IND = 'Y' 
  LEFT JOIN CRE.ORGAFDHTIO OG 
    ON Y.OG = OG.OG_ID 
  LEFT JOIN PR.SM_KY SK 
    ON y.STT_O_ID = SK.SM_KY_O_ID 
    AND SK.SMART_KEY_OBJ_TYP = 'ALYC_S' 
    AND SK.E_DT > SYSDATE
ORDER BY ANLY_S_ID ;

Amira Bedhiafi · Answer 1 · 2023-07-11T10:09:17.153

Try the following:

 SELECT DISTINCT
  STT_O_ID AS ANLY_S_ID,
  OG AS OG_ID
FROM
  (SELECT DISTINCT
    M.STT_O_ID,
    M.DEP_U_ID,
    O.OB_OF_ANA_C_ID AS OG
  FROM
    (SELECT DISTINCT
      DEP1.DEP_O_ID AS STT_O_ID,
      DEP1.DEP_O_TYP AS STA_O_TYP,
      DEP1.DEP_U_ID,
      DEP1.DEP_UP_TYP,
      LEVEL AS L2CHILD,
      CONCAT_WS('/', SYS_CONNECT_BY_PATH(DEP1.DEP_U_ID, '/')) AS PRNT2CHILD_PATH
    FROM
      (SELECT *
      FROM PR.DEPDGDGCY DEP
      WHERE DEP.DEP_UP_TYP <> 'ALYC_S'
        AND DEP.E_DT > current_date()) DEP1
    CONNECT BY NOCYCLE PRIOR DEP1.DEP_U_ID = DEP1.DEP_O_ID
      AND PRIOR DEP1.DEP_UP_TYP = DEP1.DEP_O_TYP
    START WITH DEP1.DEP_O_TYP = 'ALYC_S') M
    LEFT JOIN PR.PLE_NUE_SRC R
      ON R.PLE_ID = M.DEP_U_ID
      AND R.E_DT > current_date()
    LEFT JOIN PR.REV_SRC_IO RV
      ON RV.REV_S_ID = R.REV_S_ID
      AND RV.E_DT > current_date()
    LEFT JOIN PR.REV_GOR O
      ON RV.REV_OIGR_ID = O.REV_OIGR_ID
  ORDER BY STT_O_ID,
    OG ASC) Y
  LEFT JOIN CRE.O_NMS ORGN
    ON Y.OG = ORGN.OG_ID
    AND ORGN.CU_N_IND = 'Y'
  LEFT JOIN CRE.ORGAFDHTIO OG
    ON Y.OG = OG.OG_ID
  LEFT JOIN PR.SM_KY SK
    ON Y.STT_O_ID = SK.SM_KY_O_ID
    AND SK.SMART_KEY_OBJ_TYP = 'ALYC_S'
    AND SK.E_DT > current_date()
ORDER BY ANLY_S_ID;

Modifying SQL query to Data bricks SQL query

1 Answers1