Help Center/ Data Warehouse Service/ Standard Data Warehouse (9.1.0.x)/ DWS Performance Tuning/ SQL Tuning Examples/ Cases: Modifying Statements That Cannot Be Pushed Down/ Case: WITH-RECURSIVE Contains System Table Is Not Shippable
Updated on 2026-05-29 GMT+08:00
View PDF

Case: WITH-RECURSIVE Contains System Table Is Not Shippable

Possible Cause

The FROM clause is not complete in a branch of the RECURSIVE statement. Use the system catalog or system view (or DUAL) to replace the FROM clause.

Case 1: Using FROM DUAL Query in a RECURSIVE Branch

Original statement

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 
WITH recursive cte AS (
    SELECT 
        TO_DATE(201701, 'YYYYMM') as level ,TO_DATE(20170131, 'YYYYMMDD')  LASTDAY  
    FROM dual 

    UNION ALL 

    SELECT
        ADD_MONTHS(cte.LEVEL,   1) AS PERIOD,
        LAST_DAY(ADD_MONTHS(cte.LEVEL,   1)) AS LASTDAY
    FROM cte WHERE cte.LEVEL <=SYSDATE 
) 
SELECT 
    TO_CHAR(cte.level,'YYYYMMDD') AS PERIOD , cte.LASTDAY 
    FROM cte
WHERE TO_CHAR(cte.level,'YYYYMMDD')<= TO_CHAR(SYSDATE,'YYYYMMDD')

Rewritten statement

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 
WITH recursive cte AS (
    SELECT 
        TO_DATE(201701, 'YYYYMM') as level ,TO_DATE(20170131, 'YYYYMMDD')  LASTDAY  
    FROM generate_series(1, 1) 

    UNION ALL 

    SELECT
        ADD_MONTHS(cte.LEVEL,   1) AS PERIOD,
        LAST_DAY(ADD_MONTHS(cte.LEVEL,   1)) AS LASTDAY
    FROM cte WHERE cte.LEVEL <=SYSDATE 
) 
SELECT 
    TO_CHAR(cte.level,'YYYYMMDD') AS PERIOD , cte.LASTDAY 
    FROM cte
WHERE TO_CHAR(cte.level,'YYYYMMDD')<= TO_CHAR(SYSDATE,'YYYYMMDD')

Modification comparison

Parent Topic: Cases: Modifying Statements That Cannot Be Pushed Down