Skip to main content

Search

Welcome to Tibero GTS!

How to Convert MSSQL Recursive Query to Tibero (WITH Clause)

Document Type | Technical Information

Category | Migration

Document Number | TMITI013

 

Overview

When migrating from SQL Server to Tibero, the recursive query syntax of MSSQL differs from Tibero, requiring conversion.
This article explains how to convert the basic form of recursive queries to be usable in Tibero.
 
 

Method

MSSQL Recursive Query Example

In the query below, the TMP_USER_GROUP10 part on line 20 is the same as the WITH clause name on the first line.
In MSSQL, you can create a recursive query by calling itself within the WITH clause.
WITH TMP_USER_GROUP10 AS (
    SELECT groupCoCd
          ,groupIdnfr
          ,upperGroupIdnfr
          ,legacyPK
          ,upperLegacyPK
          ,name
          ,CONVERT(VARCHAR(1000), name) AS nameList
    FROM pms.mPMS_USER_GROUP10
    WHERE regiStusDstcd IN ('10', '20')
      AND groupCoCd = @GROUPCOCD
      AND groupIdnfr = @GROUP_IDNFR
    UNION ALL
    SELECT B.groupCoCd
          ,B.groupIdnfr
          ,B.upperGroupIdnfr
          ,B.legacyPK
          ,B.upperLegacyPK
          ,B.name
          ,CONVERT(VARCHAR(1000), CONVERT(VARCHAR(1000), B.name) + '/' + A.nameList) AS nameList
    FROM TMP_USER_GROUP10 A
    INNER JOIN pms.mPMS_USER_GROUP10 B
       ON A.groupCoCd = B.groupCoCd
      AND A.upperGroupIdnfr = B.groupIdnfr
)
SELECT @DEPTNAME = nameList
  FROM TMP_USER_GROUP10
 WHERE upperGroupIdnfr = 0;

 

Tibero Conversion Example

You can convert it to the START WITH ~ CONNECT BY format as shown below. However, to guarantee the same order as the MSSQL query, you need to add a separate ORDER BY clause.
WITH TEMP AS (
    SELECT ROWNUM AS rownu
          ,NAME
    FROM pms_LGDMOPDB.mPMS_USER_GROUP10
    WHERE regiStusDstcd IN ('10', '20')
      AND groupCoCd = v_GROUPCOCD
    START WITH groupIdnfr = v_GROUP_IDNFR
    CONNECT BY PRIOR upperGroupIdnfr = groupIdnfr
    ORDER BY rownu DESC
)
SELECT AGGR_CONCAT(NAME, '/')
  FROM TEMP;

 

Notes When Writing Tibero Queries

The method below shows how to convert to a form as close as possible to the MSSQL query without order issues.
 
In the query below, the parts in red represent the MSSQL syntax converted to Tibero syntax. Also, by listing the columns used inside the WITH clause, you can write a structure similar to the MSSQL query.
WITH TMP_USER_GROUP10 (
       groupCoCd, groupIdnfr, upperGroupIdnfr, legacyPK,
       upperLegacyPK, name, nameList) AS
(
    SELECT groupCoCd
          ,groupIdnfr
          ,upperGroupIdnfr
          ,legacyPK
          ,upperLegacyPK
          ,name
          ,CONVERT(VARCHAR(1000), name) AS nameList
    FROM pms.mPMS_USER_GROUP10
    WHERE regiStusDstcd IN ('10', '20')
      AND groupCoCd = @GROUPCOCD
      AND groupIdnfr = @GROUP_IDNFR
    UNION ALL
    SELECT B.groupCoCd
          ,B.groupIdnfr
          ,B.upperGroupIdnfr
          ,B.legacyPK
          ,B.upperLegacyPK
          ,B.name
          ,CONVERT(VARCHAR(1000),
                   CONVERT(VARCHAR(1000), B.name) + '/' + A.nameList) AS nameList
    FROM TMP_USER_GROUP10 A
    INNER JOIN pms.mPMS_USER_GROUP10 B
       ON A.groupCoCd = B.groupCoCd
      AND A.upperGroupIdnfr = B.groupIdnfr
)
SELECT @DEPTNAME = nameList
  FROM TMP_USER_GROUP10
 WHERE upperGroupIdnfr = 0;
 
 
 

Related to