1 2 Previous Next 17 Replies Latest reply on Dec 16, 2019 5:28 PM by Mark Reichman

    Explain plan will not work for curly braces in pattern clause containing consecutive{0,1}

    Mark Reichman

      I have a  "match_recognize" with a  "pattern (strt consecutive{0,1})" clause in a with clause within a larger query. I only posted the with clause portion below. The explain plan will not work because of the curly braces in the pattern line.  So how do I get an explain plan for the larger query containing this with clause?  Escape curlys somehow?

       

      I get the following error from the explain plan button in SQL Developer "Non supported SQL92 token at position: 12029"

       

      <code>

      select legal_entity_id,

             parent_le_id,

             security_id,

             transaction_start_date,

             transaction_end_date,

             shares_owned,

             eff_ownership,

             rec_type

          from combine match_recognize(

            partition by legal_entity_id, parent_le_id, security_id, eff_ownership, shares_owned order by transaction_start_date, transaction_end_date, rec_type nulls first

            measures

              first (transaction_start_date) as transaction_start_date,

              last  (transaction_end_date) as transaction_end_date,

              last  (rec_type) as rec_type

            pattern (strt consecutive{0,1})

            define

              consecutive as transaction_start_date = prev(transaction_end_date) + 1 and rec_type = 'DMS')

      </code>

        1 2 Previous Next