title: recursive WITH clause must reference itself directly in one of the UNION ALL branches
body: <p>The following query is giving error : <strong>recursive WITH clause must reference itself directly in one of the UNION ALL branches</strong></p>
  
  <pre><code>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                    &lt;= 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 &lt; 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 &lt;= 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 &lt;= V_MAX_COUNT
          ORDER BY Q.GL_ACCOUNT_KEY;
  </code></pre>
  
  <p>i have no idea how to solve.. any suggestion would be helpful</p>