文档首页/ 数据仓库服务 DWS/ 更多文档/ 开发指南（联盟区域）/ DWS性能调优/ SQL调优案例/ 案例：典型不下推语句整改案例集/ 案例：With-Recursive contains only values rte is not shippable
更新时间：2025-12-12 GMT+08:00
查看PDF
分享

案例：With-Recursive contains only values rte is not shippable

问题根因

递归语句的某个分支中没有FROM子句（只有 VALUES 或者类似 SELECT 1 这样的语句）。

案例1：递归驱动分支没有FROM子句

原始语句

 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
 
SELECT  T.RPT_ITEM_ID, --报表项ID
    T.RPT_ITEM_CODE,
    T.USER_GROUP_CODE AS USER_GROUP_CODE --用户组
FROM BIF.BIF_RPT_ITEM_DEF_T T,
(WITH recursive cte AS (
        SELECT DISTINCT TRIM(SUBSTR('' :: text, INSTR('', ',', 1, 1) + 1, INSTR('', ',', 1, 2) - INSTR('', ',', 1, 1) - 1)) AS cte_RPT_ITEM_CODE, 
            1 AS level
        FROM (SELECT '') AS tb0

        UNION ALL

        SELECT DISTINCT TRIM(SUBSTR('' :: text, INSTR('', ',', 1, cte.level + 1) + 1, INSTR('', ',', 1, cte.level + 2) - INSTR('', ',', 1, cte.level + 1) - 1)),
            cte.level + 1
        FROM (SELECT '') AS tb0, cte
        WHERE cte.level + 1 <= LENGTH('') - LENGTH(REPLACE('', ',', '')) - 1
    )
    SELECT 
        DISTINCT cte_RPT_ITEM_CODE AS RPT_ITEM_CODE 
    FROM cte
) T5
WHERE NVL(INSTR(T.RPT_ITEM_FREQUENCE, 'M'), 0) > 0
AND T.RPT_ITEM_CODE = NVL(T5.RPT_ITEM_CODE, T.RPT_ITEM_CODE)
AND T.RPT_ITEM_TYPE = 1 --是否是叶子报表项，1=是，0=否，基本报表项
AND T.ENABLE_FLAG = 1
AND T.VERSION = '202308' --使用快照,增加条件限制
ORDER BY T.RPT_ITEM_ID

改写语句

 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
 
SELECT  T.RPT_ITEM_ID, --报表项ID
    T.RPT_ITEM_CODE,
    T.USER_GROUP_CODE AS USER_GROUP_CODE --用户组
FROM BIF.BIF_RPT_ITEM_DEF_T T,
(WITH recursive cte AS (
        SELECT DISTINCT TRIM(SUBSTR('' :: text, INSTR('', ',', 1, 1) + 1, INSTR('', ',', 1, 2) - INSTR('', ',', 1, 1) - 1)) AS cte_RPT_ITEM_CODE, 
            1 AS level
        FROM generate_series(1, 1) AS tb0

        UNION ALL

        SELECT DISTINCT TRIM(SUBSTR('' :: text, INSTR('', ',', 1, cte.level + 1) + 1, INSTR('', ',', 1, cte.level + 2) - INSTR('', ',', 1, cte.level + 1) - 1)),
            cte.level + 1
        FROM (SELECT '') AS tb0, cte
        WHERE cte.level + 1 <= LENGTH('') - LENGTH(REPLACE('', ',', '')) - 1
    )
    SELECT 
        DISTINCT cte_RPT_ITEM_CODE AS RPT_ITEM_CODE 
    FROM cte
) T5
WHERE NVL(INSTR(T.RPT_ITEM_FREQUENCE, 'M'), 0) > 0
AND T.RPT_ITEM_CODE = NVL(T5.RPT_ITEM_CODE, T.RPT_ITEM_CODE)
AND T.RPT_ITEM_TYPE = 1 --是否是叶子报表项，1=是，0=否，基本报表项
AND T.ENABLE_FLAG = 1
AND T.VERSION = '202308' --使用快照,增加条件限制
ORDER BY T.RPT_ITEM_ID

修改点比对

案例2：递归驱动分支没有FROM子句

原始语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
SELECT A.DYNM_COMP_ID,
    DECODE(B.LINE_NO, 1, '202308', A.VERSION)
FROM BIF.BIF_DYNM_COMP_SOU_TBL_V A,
(WITH recursive cte AS (
    SELECT 1 AS level
    UNION ALL
    SELECT cte.level + 1
    FROM cte
    WHERE cte.level + 1 < 3
    )
    SELECT 
        level as LINE_NO 
    FROM cte
) B
WHERE EXISTS (SELECT 1
    FROM BIF.BIF_RPT_ITEM_DEF_MV RPT,
    BIF.BIF_PUB_SUBJECT_AREA_T SBJ,
    BIF.BIF_SNAPSHORT_SUBJECT_V TYP
    WHERE A.DYNM_COMP_ID = RPT.DYNM_COMP_ID
    AND RPT.VERSION = 'current'
    AND RPT.SUBJECT_AREA_ID = SBJ.SUBJECT_AREA_ID
    AND SBJ.SUBJECT_AREA_CODE =TYP.SUBJECT_CODE
    AND TYP.SUBJECT_TYPE ='TAX')
AND A.VERSION = 'current'

改写语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 
SELECT A.DYNM_COMP_ID,
    DECODE(B.LINE_NO, 1, '202308', A.VERSION)
FROM BIF.BIF_DYNM_COMP_SOU_TBL_V A,
(SELECT 
    * 
    FROM generate_series(1, 2) AS cte(LINE_NO)
) B
WHERE EXISTS (SELECT 1
    FROM BIF.BIF_RPT_ITEM_DEF_MV RPT,
    BIF.BIF_PUB_SUBJECT_AREA_T SBJ,
    BIF.BIF_SNAPSHORT_SUBJECT_V TYP
    WHERE A.DYNM_COMP_ID = RPT.DYNM_COMP_ID
    AND RPT.VERSION = 'current'
    AND RPT.SUBJECT_AREA_ID = SBJ.SUBJECT_AREA_ID
    AND SBJ.SUBJECT_AREA_CODE =TYP.SUBJECT_CODE
    AND TYP.SUBJECT_TYPE ='TAX')
AND A.VERSION = 'current'

修改点比对

案例3：递归驱动分支是VALUES子句

原始语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
WITH RECURSIVE t(n) AS (
    VALUES (1)

    UNION ALL

    SELECT 
        n+1 
    FROM t 
    WHERE n < (SELECT MAX(LENGTH(COMP_CODE)-LENGTH(REPLACE(COMP_CODE,',','')))+1 MAX_TOKENS
                                       FROM (SELECT DEPT_CODE,
                                                    to_char(APPLICABLE_GEO_PC_CODE) COMP_CODE
                                               FROM SDIHR.MDM_CDM_DEPT_ACT_INFO_T_3600)
    )
)
SELECT n AS LVL FROM t

改写语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
WITH RECURSIVE t(n) AS (
    SELECT * FROM generate_series(1, 1)

    UNION ALL

    SELECT 
        n+1 
    FROM t 
    WHERE n < (SELECT MAX(LENGTH(COMP_CODE)-LENGTH(REPLACE(COMP_CODE,',','')))+1 MAX_TOKENS
                                       FROM (SELECT DEPT_CODE,
                                                    to_char(APPLICABLE_GEO_PC_CODE) COMP_CODE
                                               FROM SDIHR.MDM_CDM_DEPT_ACT_INFO_T_3600)
    )
)
SELECT n AS LVL FROM t

修改点比对

案例4：递归驱动分支是VALUES子句

原始语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
WITH RECURSIVE t(n) AS (
    VALUES (1)

    UNION ALL

    SELECT 
        n+1 
    FROM t 
    WHERE n < (SELECT MAX(LENGTH(COMP_CODE)-LENGTH(REPLACE(COMP_CODE,',','')))+1 MAX_TOKENS
    FROM (SELECT DEPT_CODE,
     to_char(APPLICABLE_GEO_PC_CODE) COMP_CODE
                                               FROM SDIHR.MDM_CDM_DEPT_ACT_INFO_T_3600))
)
SELECT n AS LVL FROM t

改写语句

1
2
3
4
5
6
7
 
SELECT 
 * 
FROM generate_series(1, (SELECT MAX(LENGTH(COMP_CODE)-LENGTH(REPLACE(COMP_CODE,',','')))+1 MAX_TOKENS
    FROM (SELECT DEPT_CODE,
    to_char(APPLICABLE_GEO_PC_CODE) COMP_CODE
    FROM SDIHR.MDM_CDM_DEPT_ACT_INFO_T_3600))
) AS t(lvl)

修改点比对