Oracle Recursive to SQL Server？

Question

我正在尝试将通常在oracle服务器上运行的查询转换为使用SQL server (使用Teradata SQL Assistant客户端)，但我在理解语法更改是什么方面遇到了问题。

目前针对Oracle，它生成具有相关计划工作的“父”设备列表，同时还生成与“子”设备it相关的任何计划工作。

SELECT
EquipmentSurfaceAssetBK AS "DEPARTMENT", 
ParentEquipmentCodeBK AS "PARENT",
EquipmentCodeBK AS "POSITION",
EquipmentDescription AS "POSITION_DESC",
EquipmentCostCode AS "COSTCODE",
EqPMCodeBK AS "PM",
PMDescription AS "PM_DESC",
PMWorkOrderJobType AS "PM_CLASS",
EqPMAssignedToResourceBK AS "PM_ASSIGNED",
EqPMAssignedBy AS "PM_SCHEDULER",
EqPMDueDate AS "NEXT_DUE",
EQPMDeactivatedDate AS "DEACTIVED_DATE",
EqPMFrequency AS "FREQUENCY",
EQPMFrequencyUOM AS "FREQ_UOM"
FROM    (IDW_PL_SURFACEMAINTENANCE.DIMEquipmentPM JOIN IDW_PL_SURFACEMAINTENANCE.DIMSurfaceEquipment ON IDW_PL_SURFACEMAINTENANCE.DIMEquipmentPM.EqPMEquipmentCodeBK = IDW_PL_SURFACEMAINTENANCE.DIMSurfaceEquipment.EquipmentCodeBK) 
        JOIN IDW_PL_SURFACEMAINTENANCE.DIMSurfacePM ON IDW_PL_SURFACEMAINTENANCE.DIMSurfacePM.PMCodeBK = IDW_PL_SURFACEMAINTENANCE.DIMEquipmentPM.EqPMCodeBK
    WHERE IDW_PL_SURFACEMAINTENANCE.DIMEquipmentPM.EqPMCodeBK IS NOT NULL
    START 
            WITH ParentEquipmentCodeBK IN 
        (Select
            EquipmentCodeBK
            From IDW_PL_SURFACEMAINTENANCE.DIMSurfaceEquipment 
            Where EquipmentCodeBK LIKE 'SA-PLT-WP')
        CONNECT BY PRIOR EquipmentCodeBK = ParentEquipmentCodeBK
        ORDER BY PARENT 

Answer 1

下面是Oracle中一个简单的CONNECT BY以及与其等效的递归WITH。您可以将相同的转换应用于上面的版本

SQL>   select level, empno, mgr
  2  from   scott.emp
  3  start with mgr is null
  4  connect by prior empno = mgr;

     LEVEL      EMPNO        MGR
---------- ---------- ----------
         1       7839
         2       7566       7839
         3       7788       7566
         4       7876       7788
         3       7902       7566
         4       7369       7902
         2       7698       7839
         3       7499       7698
         3       7521       7698
         3       7654       7698
         3       7844       7698
         3       7900       7698
         2       7782       7839
         3       7934       7782

14 rows selected.

SQL>
SQL>
SQL> with emps(lev, empno, mgr) as
  2  ( select 1 lev, empno, mgr
  3    from  scott.emp
  4    where mgr is null
  5    union all
  6    select h.lev+1, e.empno, e.mgr
  7    from   scott.emp e, emps h
  8    where  h.empno = e.mgr
  9  )
 10  select * from emps;

       LEV      EMPNO        MGR
---------- ---------- ----------
         1       7839
         2       7566       7839
         2       7698       7839
         2       7782       7839
         3       7788       7566
         3       7902       7566
         3       7499       7698
         3       7521       7698
         3       7654       7698
         3       7844       7698
         3       7900       7698
         3       7934       7782
         4       7876       7788
         4       7369       7902

14 rows selected.