Help Center/ GaussDB(DWS)/ Tool Guide/ DSC/ SQL Syntax Migration Reference/ Oracle Syntax Migration/ CONNECT BY
Updated on 2024-07-19 GMT+08:00
View PDF
Share

CONNECT BY

Input-CONNECT BY

1
2
3
4
5
6
7
8
9
 
SELECT id FROM city_branch start with id=roleBranchId connect by prior id=parent_id;
SELECT T.BRANCH_LEVEL, t.ID
                    FROM city_branch c
                   WHERE     (c.branch_level = '1' OR T.BRANCH_LEVEL = '2')
                         AND (T.SIGN = '1' OR T.SIGN = '4' OR T.SIGN = '8')
                         AND T.STATUS = '1'
              START WITH c.ID = I_BRANCH_ID
              CONNECT BY c.ID = PRIOR c.parent_id
                ORDER BY c.branch_level DESC ;

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
WITH RECURSIVE migora_cte AS (
     SELECT
               id
               ,1 AS LEVEL
          FROM
               city_branch
          WHERE
               id = roleBranchId
     UNION
     ALL SELECT
             mig_ora_cte_join_alias.id
             ,mig_ora_cte_tab_alias.LEVEL + 1 AS LEVEL
          FROM
             migora_cte mig_ora_cte_tab_alias INNER JOIN city_branch mig_ora_cte_join_alias
                 ON mig_ora_cte_tab_alias.id = mig_ora_cte_join_alias.parent_id
) SELECT
          id
     FROM
          migora_cte
     ORDER BY
          LEVEL
;
 
WITH RECURSIVE migora_cte AS (
     SELECT
               BRANCH_LEVEL
               ,ID
               ,SIGN
               ,STATUS
               ,parent_id
               ,1 AS LEVEL
          FROM
               city_branch c
          WHERE
               c.ID = I_BRANCH_ID
     UNION
     ALL SELECT
               c.BRANCH_LEVEL
               ,c.ID
               ,c.SIGN
               ,c.STATUS
               ,c.parent_id
               ,mig_ora_cte_tab_alias.LEVEL + 1 AS LEVEL
          FROM
               migora_cte mig_ora_cte_tab_alias INNER JOIN city_branch c
                    ON c.ID = mig_ora_cte_tab_alias.parent_id
) SELECT
          BRANCH_LEVEL
          ,ID
     FROM
          migora_cte c
     WHERE
          (
               c.branch_level = '1'
               OR T.BRANCH_LEVEL = '2'
          )
          AND( T.SIGN = '1' OR T.SIGN = '4' OR T.SIGN = '8' )
          AND T.STATUS = '1'
     ORDER BY
          c.branch_level DESC
;

Input - CONNECT BY multiple tables

The syntax shows the relationship between each child row and its parent row. It uses the CONNECT BY xxx PRIOR clause to define the relationship between the current row (child row) and the previous row (parent row).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
SELECT DISTINCT a.id menuId,
               F.name menuName,
               a.status menuState,
               a.parent_id menuParentId,
               '-1' menuPrivilege,
               a.serialNo menuSerialNo
  FROM CTP_MENU a, CTP_MENU_NLS F
  START WITH a.serialno in (1, 2, 3)
CONNECT BY a.id = PRIOR a.parent_id
       AND f.locale = Language
       AND a.id = f.id
ORDER BY menuId, menuParentId;

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
WITH RECURSIVE migora_cte AS (
        SELECT pr.service_product_id
             , t.enabled_flag
             , pr.operation_id
             , pr.enabled_flag
             , pr.product_code
             , 1 AS LEVEL
          FROM asms.cppsv_operation_sort t
             , asms.cppsv_product_class pr
         WHERE level_id = 3
           AND pr.operation_id = t.operation_id(+)
      UNION ALL 
         SELECT pr.service_product_id
              , t.enabled_flag
              , pr.operation_id
              , pr.enabled_flag
              , pr.product_code
              , mig_ora_cte_tab_alias.LEVEL + 1 AS LEVEL
           FROM migora_cte mig_ora_cte_tab_alias
              , asms.cppsv_operation_sort t
              , asms.cppsv_product_class pr
          WHERE mig_ora_cte_tab_alias.service_product_id = pr.service_product_father_id
            AND pr.operation_id = t.operation_id(+) ) 
SELECT pr.service_product_id
  FROM migora_cte
 WHERE nvl( UPPER( enabled_flag ) ,'Y' ) = 'Y'
   AND nvl( enabled_flag ,'Y' ) = 'Y'
   AND pr.product_code = rec_product1.service_product_code
 ORDER BY LEVEL;
Parent topic: Oracle Syntax Migration