recursive WITH clause must reference itself directly in one of the UNION ALL branches

Question

The following query is giving error : recursive WITH clause must reference itself directly in one of the UNION ALL branches

insert into tt_q_items
 WITH CTE 
  (
        REFUND_TRANSMISSION_QUEUE_KEY,
        FINANCIAL_REFUND_REQUEST_KEY,
        FINANCIAL_TRANSACTION_KEY,
        TRANSACTION_STATUS_KEY,
        REFUND_AMOUNT,
        TOTAL
  ) 
  AS
  (
        SELECT Q.refund_transmission_queue_key ,
        req.financial_refund_request_key ,
        txn.financial_transaction_key ,
        txn.transaction_status_key ,
        ABS(TXN.COLLECTION_AMOUNT) REFUND_AMOUNT ,
        ABS(txn.collection_amount) total
        FROM its_refund_transmission_queue Q
        JOIN its_financial_refund_request req
        ON req.financial_refund_request_key = Q.financial_transaction_key
        JOIN its_financial_transaction txn
        ON txn.linked_transaction_key = Q.financial_transaction_key
        JOIN r_financial_trans_type_x_cat TC
        ON TC.fin_transaction_type_key = txn.fin_transaction_type_key
        JOIN r_financial_tran_category C
        ON C.financial_tran_category_key = TC.financial_tran_category_key
        JOIN its_account acct
        ON acct.account_key            = txn.account_key
        WHERE C.code                   = 'REFUND'
        AND v_account_type_key        IS NULL
        OR acct.account_type_key       = v_account_type_key
        AND v_account_subtype_key     IS NULL
        OR acct.account_subtype_key    = v_account_subtype_key
        AND Q.offline_status_key       = v_offline_status_unprocessed
        AND TXN.TRANSACTION_STATUS_KEY = V_PENDTRANSMSN_TRANS_STATUS_KE
        AND ROWNUM                    <= 1
  UNION ALL
        SELECT 
     r.refund_transmission_queue_key,
         r.financial_refund_request_key, 
         r.financial_transaction_key, 
         r.transaction_status_key, 
        cast(REFUND_AMOUNT AS NUMBER(19,4)) ,
        CAST(TOTAL AS NUMBER(19,4)) total
        FROM
        (
            SELECT Q.refund_transmission_queue_key ,
            req.financial_refund_request_key ,
            txn.financial_transaction_key ,
            txn.transaction_status_key ,
            ABS(TXN.COLLECTION_AMOUNT) REFUND_AMOUNT ,
            (ABS(txn.collection_amount)+  CTE.total) total ,
            ROW_NUMBER() OVER ( ORDER BY Q.refund_transmission_queue_key ) rn
            FROM its_refund_transmission_queue Q
            JOIN its_financial_refund_request req
            ON req.financial_refund_request_key = Q.financial_transaction_key
            JOIN its_financial_transaction txn
            ON TXN.LINKED_TRANSACTION_KEY = Q.FINANCIAL_TRANSACTION_KEY
            JOIN r_financial_trans_type_x_cat TC
            ON TC.fin_transaction_type_key = txn.fin_transaction_type_key
            JOIN r_financial_tran_category C
            ON C.FINANCIAL_TRAN_CATEGORY_KEY = TC.FINANCIAL_TRAN_CATEGORY_KEY
              JOIN CTE r
                  ON  r.REFUND_TRANSMISSION_QUEUE_KEY < Q.REFUND_TRANSMISSION_QUEUE_KEY
                 WHERE TXN.TRANSACTION_STATUS_KEY   = V_PENDTRANSMSN_TRANS_STATUS_KE 
            AND C.CODE = 'REFUND'
        ) r
        WHERE   r.RN   = 1
        AND total <= v_max_dollars

  )

        SELECT Q.refund_transmission_queue_key ,
        Q.gl_account_key ,
        CTE.financial_refund_request_key ,
        CTE.financial_transaction_key ,
        CTE.transaction_status_key ,
        CTE.refund_amount
        FROM CTE
        JOIN its_refund_transmission_queue Q
        ON Q.refund_transmission_queue_key = CTE.refund_transmission_queue_key
        WHERE Q.offline_status_key         = v_offline_status_unprocessed
        AND CTE.transaction_status_key     = v_pendtransmsn_trans_status_ke 
        AND ROWNUM <= V_MAX_COUNT
        ORDER BY Q.GL_ACCOUNT_KEY;

i have no idea how to solve.. any suggestion would be helpful

What is the exception stack (complete error message) that you are getting? — Rachcha, Sep 05 '13 at 13:03
ORA-32042: recursive WITH clause must reference itself directly in one of the UNION ALL branches — Shankari vatsalkumar, Sep 06 '13 at 05:29
In your code, at line 53 you have coded `(ABS(txn.collection_amount)+ CTE.total) total ,` and on line 64 you have given `JOIN CTE r`. Both these are inside the inline view of the second query in the `UNION ALL` clause. Bring the call to `CTE` outside this inline view and everything should work fine. — Rachcha, Sep 06 '13 at 08:09
i removed cte from cte.total.. still same error.. how can i bring cte outside.. — Shankari vatsalkumar, Sep 06 '13 at 08:52
Take a look at your functional requirement. (If possible, please post it here.) If the function requires a recursive inline view then try to find out how you can avoid the recursion. BTW removing CTE from CTE.total will not help. You will have to take the JOIN CTE statement outside the inline view. — Rachcha, Sep 06 '13 at 10:20

score 2 · Accepted Answer · answered Oct 23 '13 at 08:25

You must bring CTE out of inline view like this:

    SELECT 
       r.refund_transmission_queue_key,
       r.financial_refund_request_key, 
       r.financial_transaction_key, 
       r.transaction_status_key, 
       cast(refund_amount as number(19,4)) ,
       CAST(TOTAL + r1.total AS NUMBER(19,4)) total
    FROM
    (
        select q.refund_transmission_queue_key ,
               req.financial_refund_request_key ,
               txn.financial_transaction_key ,
               txn.transaction_status_key ,
               ABS(TXN.COLLECTION_AMOUNT) REFUND_AMOUNT ,
               ABS(txn.collection_amount) total ,
               ROW_NUMBER() OVER ( ORDER BY Q.refund_transmission_queue_key ) rn
        FROM its_refund_transmission_queue Q
        JOIN its_financial_refund_request req
        ON req.financial_refund_request_key = Q.financial_transaction_key
        JOIN its_financial_transaction txn
        ON TXN.LINKED_TRANSACTION_KEY = Q.FINANCIAL_TRANSACTION_KEY
        JOIN r_financial_trans_type_x_cat TC
        ON TC.fin_transaction_type_key = txn.fin_transaction_type_key
        JOIN r_financial_tran_category C
        ON C.FINANCIAL_TRAN_CATEGORY_KEY = TC.FINANCIAL_TRAN_CATEGORY_KEY
        WHERE TXN.TRANSACTION_STATUS_KEY   = V_PENDTRANSMSN_TRANS_STATUS_KE 
          AND C.CODE = 'REFUND'
    ) r
    JOIN cte r1
    ON  r1.REFUND_TRANSMISSION_QUEUE_KEY < r.REFUND_TRANSMISSION_QUEUE_KEY
    where   r.rn   = 1
    AND total + r1.total <= v_max_dollars;

recursive WITH clause must reference itself directly in one of the UNION ALL branches

1 Answers1