# 案例：With-Recursive contains only values rte is not shippable
#### 问题根因
递归语句的某个分支中没有FROM子句（只有 VALUES 或者类似 SELECT 1 这样的语句）。
#### 案例1：递归驱动分支没有FROM子句
**原始语句**
```
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
```
**改写语句**
```
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
```
**修改点比对**
![](https://support.huaweicloud.com/devg-dws/figure/zh-cn_image_0000002525799813.png "点击放大")
#### 案例2：递归驱动分支没有FROM子句
**原始语句**
```
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'
```
**改写语句**
```
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'
```
**修改点比对**
![](https://support.huaweicloud.com/devg-dws/figure/zh-cn_image_0000002493600422.png "点击放大")
#### 案例3：递归驱动分支是VALUES子句
**原始语句**
```
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_DEPT_ACT_INFO_T_3600)
    )
)
SELECT n AS LVL FROM t
```
**改写语句**
```
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_DEPT_ACT_INFO_T_3600)
    )
)
SELECT n AS LVL FROM t
```
**修改点比对**
![](https://support.huaweicloud.com/devg-dws/figure/zh-cn_image_0000002525800527.png "点击放大")
#### 案例4：递归驱动分支是VALUES子句
**原始语句**
```
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_DEPT_ACT_INFO_T_3600))
) SELECT n AS LVL FROM t
```
**改写语句**
```
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_DEPT_ACT_INFO_T_3600))
) AS t(lvl)
```
**修改点比对**
![](https://support.huaweicloud.com/devg-dws/figure/zh-cn_image_0000002525920565.png "点击放大")