How to translate "connect by" ORACLE to SQL Server

Question

When using a RECURSIVE CTE in SQL Server, GROUP BY, LEFT OUTER JOIN and ORDER BY etc should not be used.

How can I translate this ORACLE query to SQL Server?

SELECT *
  FROM (SELECT COALESCE(a.tree_id, ' ') MNUID,
                COALESCE(a.tree_nm, ' ') MNUNM,
                COALESCE(A.TREE_LEV_CD, ' ') MNULEVCD,
                COALESCE(A.UP_TREE_ID, ' ') UPMNUID,
                COALESCE(A.TREE_CD, ' ') TREECD,
                COALESCE(A.TREE_LRK_RUF_ID, ' ') SCEXRSSEQVL,
                CASE WHEN B.SCH_CNT=NULL THEN 0 ELSE B.SCH_CNT END AS SCHCNT
        FROM SC_TREE_MASTER A
        LEFT OUTER JOIN
            (SELECT C.TREE_LRK_RUF_ID, COUNT(SCH_ID) SCH_CNT
                FROM SC_TREE_MASTER C
                LEFT OUTER JOIN AL_SCHEDULE_MAPPING D
                ON C.TREE_LRK_RUF_ID = D.PRA_WF_ID
                WHERE C.TREE_BJ_CD = '01'
                AND C.TREE_CD = '04'
                GROUP BY TREE_LRK_RUF_ID) B
        ON (A.TREE_BJ_CD = '01' AND A.TREE_LRK_RUF_ID = B.TREE_LRK_RUF_ID)
        ORDER BY MNULEVCD, SCEXRSSEQVL)
 START WITH UPMNUID = 'TR00000000'
CONNECT BY PRIOR MNUID = UPMNUID
 ORDER SIBLINGS BY MNUID;

I tried so far.

But Error is occurred.

WITH TEMP_TAB(MNUID, MNUNM, MNULEVCD, UPMNUID, TREECD, TREE_LRK_RUF_ID, SORT, RECURSIVE_LEVEL) AS            
 (SELECT TREE_ID,
         TREE_NM,
         TREE_LEV_CD,
         UP_TREE_ID,
         TREE_CD,
         TREE_LRK_RUF_ID,
         CONCAT('-',TREE_ID) AS SORT,
         1 AS RECURSIVE_LEVEL
    FROM SC_TREE_MASTER 
   WHERE TREE_BJ_CD = '01'
     AND UP_TREE_ID = 'TR00000000'
  UNION ALL
  SELECT A.TREE_ID,
         A.TREE_NM,
         A.TREE_LEV_CD,
         A.UP_TREE_ID,
         A.TREE_CD,
         A.TREE_LRK_RUF_ID,
         CONCAT(B.SORT,'/',A.TREE_ID) AS SORT,
         (RECURSIVE_LEVEL + 1) AS RECURSIVE_LEVEL
    FROM SC_TREE_MASTER A, TEMP_TAB B
   WHERE A.TREE_BJ_CD = '01'
     AND B.MNUID =A.UP_TREE_ID 
 )
SELECT COALESCE(M.TREE_ID, ' ') MNUID,
       COALESCE(M.TREE_NM, ' ') MNUNM,
       COALESCE(M.TREE_LEV_CD, ' ') MNULEVCD,
       COALESCE(M.UP_TREE_ID, ' ') UPMNUID,
       COALESCE(M.TREE_CD, ' ') TREECD,
       COALESCE(M.TREE_LRK_RUF_ID, ' ') TREE_LRK_RUF_ID
FROM TEMP_TAB M                
LEFT OUTER JOIN (SELECT C.TREE_LRK_RUF_ID, COUNT(SCH_ID) SCH_CNT
                  FROM SC_TREE_MASTER C
                  LEFT OUTER JOIN AL_SCHEDULE_MAPPING D
                    ON C.TREE_LRK_RUF_ID = D.PRA_WF_ID
                 WHERE C.TREE_BJ_CD = '01'
                   AND C.TREE_CD = '04'
                 GROUP BY TREE_LRK_RUF_ID) N
ON M.TREE_LRK_RUF_ID = N.TREE_LRK_RUF_ID 
ORDER BY SORT;

Types don't match between the anchor and the recursive part in column "SORT" of recursive query "TEMP_TAB".

Another question: how can I translate connect by in ORACLE to ANSI for all database systems?

Isn't it possible??

I added this query which can be used in POSTGRESQL in below.

I think this query is similar with SQL Server RECURSIVE-CTE query, but in SQL Server, it doesn't work.

WITH RECURSIVE TEMP_TAB(TREE_ID,
         TREE_NM,
         TREE_LEV_CD,
         UP_TREE_ID,
         TREE_CD,
         TREE_LRK_RUF_ID,
         SORT ,                         
         RECURSIVE_LEVEL) AS            
 (SELECT TREE_ID,
         TREE_NM,
         TREE_LEV_CD,
         UP_TREE_ID,
         TREE_CD,
         TREE_LRK_RUF_ID,
         CONCAT('-',TREE_ID) ,
         1 RECURSIVE_LEVEL
    FROM SC_TREE_MASTER 
   WHERE TREE_BJ_CD = '01'
     AND UP_TREE_ID = 'TR00000000'
  UNION ALL
  SELECT A.TREE_ID,
         A.TREE_NM,
         A.TREE_LEV_CD,
         A.UP_TREE_ID,
         A.TREE_CD,
         A.TREE_LRK_RUF_ID,
         CONCAT(B.SORT,'/',A.TREE_ID),
         RECURSIVE_LEVEL + 1
    FROM SC_TREE_MASTER A, TEMP_TAB B
   WHERE A.TREE_BJ_CD = '01'
     AND B.TREE_ID =A.UP_TREE_ID 
 )
SELECT COALESCE(M.TREE_ID, ' ') MNUID,
       COALESCE(M.TREE_NM, ' ') MNUNM,
       COALESCE(M.TREE_LEV_CD, ' ') MNULEVCD,
       COALESCE(M.UP_TREE_ID, ' ') UPMNUID,
       COALESCE(M.TREE_CD, ' ') TREECD,
       COALESCE(M.TREE_LRK_RUF_ID, ' ') SCEXRSSEQVL,
       COALESCE(N.SCH_CNT, 0)
FROM TEMP_TAB M                
LEFT OUTER JOIN (SELECT C.TREE_LRK_RUF_ID, COUNT(SCH_ID) SCH_CNT
                  FROM SC_TREE_MASTER C
                  LEFT OUTER JOIN AL_SCHEDULE_MAPPING D
                    ON C.TREE_LRK_RUF_ID = D.PRA_WF_ID
                 WHERE C.TREE_BJ_CD = '01'
                   AND C.TREE_CD = '04'
                 GROUP BY TREE_LRK_RUF_ID) N
ON M.TREE_LRK_RUF_ID = N.TREE_LRK_RUF_ID 
ORDER BY SORT;

SQL Server does not like the `recursive` keyword (even though it is required in the SQL standard). If you remove it, it should be fine. — , May 21 '14 at 06:50
@a_horse_with_no_name It doesn't work. In my query, Something is wrong. — user2427306, May 21 '14 at 07:08
You could try casting it explicitly to a varchar of a certain length, say 100 (I don't know if this is long enough for your case), so `CAST(CONCAT('-',TREE_ID) AS VARCHAR(100))`, and the same for the recursive column. — NickyvV, May 21 '14 at 07:12
"*Something is wrong*" is not a valid error message for SQL Server. — , May 21 '14 at 07:13
@a_horse_with_no_name My English is bad. Anyway, recursive keyword is useless in MSSQL. Thank you so much!!! — user2427306, May 22 '14 at 07:45

score 0 · Answer 1 · answered May 22 '14 at 07:45

WITH TEMP_TAB(TREE_ID,
         TREE_NM,
         TREE_LEV_CD,
         UP_TREE_ID,
         TREE_CD,
         TREE_LRK_RUF_ID,
         SORT,                         
         RECURSIVE_LEVEL) AS            
 (SELECT TREE_ID,
         TREE_NM,
         TREE_LEV_CD,
         UP_TREE_ID,
         TREE_CD,
         TREE_LRK_RUF_ID,
         CAST(CONCAT('-',TREE_ID) AS VARCHAR(100)),
         1 RECURSIVE_LEVEL
    FROM SC_TREE_MASTER 
   WHERE TREE_BJ_CD = '01'
     AND UP_TREE_ID = 'TR00000000'
  UNION ALL
  SELECT A.TREE_ID,
         A.TREE_NM,
         A.TREE_LEV_CD,
         A.UP_TREE_ID,
         A.TREE_CD,
         A.TREE_LRK_RUF_ID,
         CAST(CONCAT(B.SORT,'/',A.TREE_ID) AS VARCHAR(100)),
         RECURSIVE_LEVEL + 1
    FROM SC_TREE_MASTER A, TEMP_TAB B
   WHERE A.TREE_BJ_CD = '01'
     AND B.TREE_ID =A.UP_TREE_ID 
 )
SELECT COALESCE(M.TREE_ID, ' ') MNUID,
       COALESCE(M.TREE_NM, ' ') MNUNM,
       COALESCE(M.TREE_LEV_CD, ' ') MNULEVCD,
       COALESCE(M.UP_TREE_ID, ' ') UPMNUID,
       COALESCE(M.TREE_CD, ' ') TREECD,
       COALESCE(M.TREE_LRK_RUF_ID, ' ') SCEXRSSEQVL,
       COALESCE(N.SCH_CNT, 0)
FROM TEMP_TAB M                
LEFT OUTER JOIN (SELECT C.TREE_LRK_RUF_ID, COUNT(SCH_ID) SCH_CNT
                  FROM SC_TREE_MASTER C
                  LEFT OUTER JOIN AL_SCHEDULE_MAPPING D
                    ON C.TREE_LRK_RUF_ID = D.PRA_WF_ID
                 WHERE C.TREE_BJ_CD = '01'
                   AND C.TREE_CD = '04'
                 GROUP BY TREE_LRK_RUF_ID) N
ON M.TREE_LRK_RUF_ID = N.TREE_LRK_RUF_ID 
ORDER BY SORT;

How to translate "connect by" ORACLE to SQL Server

1 Answers1