4 Replies Latest reply on Dec 13, 2018 5:32 AM by mNem

    Non supported SQL92 token is thrown for {0,1} in match_recognize

    mNem

      When invoking the below query with F5 (Run Script) I get ...

       

      with t (site, meter_code, action_dt, action) as 
      (
      select 1, 'MTR1', date '2018-01-01', 'IN' from dual union all
      select 1, 'MTR1', date '2018-02-01', 'READ' from dual union all
      select 1, 'MTR1', date '2018-03-01', 'READ' from dual union all
      select 1, 'MTR1', date '2018-04-01', 'OUT'  from dual union all
      select 1, 'MTR1', date '2018-05-01', 'IN'  from dual union all
      select 1, 'MTR1', date '2018-06-01', 'OUT' from dual
      )
      select * from t
      match_recognize
      (
      partition by site, meter_code
      order by action_dt
      measures  match_number() mn
      pattern (ENTER OTHER* EX{0,1})
      define
         ENTER as action = 'IN'
         , OTHER as action  != 'OUT'
         , EX as action = 'OUT'
      )
      ;
      

       

      Error report -

      Non supported SQL92 token at position: 559

       

      The same query works fine for F9 (Run Statement).

       

      SQL Developer version: Version 18.3.0.277 Build 277.2354

       

      BANNER                                                                         

      --------------------------------------------------------------------------------

      Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

        • 1. Re: Non supported SQL92 token is thrown for {0,1} in match_recognize
          mNem

          The alternative works fine with F5 but not with F9.

           

          with t (site, meter_code, action_dt, action) as 
          (
          select 1, 'MTR1', date '2018-01-01', 'IN' from dual union all
          select 1, 'MTR1', date '2018-02-01', 'READ' from dual union all
          select 1, 'MTR1', date '2018-03-01', 'READ' from dual union all
          select 1, 'MTR1', date '2018-04-01', 'OUT'  from dual union all
          select 1, 'MTR1', date '2018-05-01', 'IN'  from dual union all
          select 1, 'MTR1', date '2018-06-01', 'OUT' from dual
          )
          select * from t
          match_recognize
          (
          partition by site, meter_code
          order by action_dt
          measures ENTER.ACTION_DT as d1, EX.ACTION_DT as d2, match_number() mn
          pattern (ENTER OTHER* EX{\?\})
          define
               ENTER as action = 'IN'
             , OTHER as action  != 'OUT'
             , EX as action = 'OUT'
          )
          ;
          

           

                SITE METE D1         D2                 MN

          ---------- ---- ---------- ---------- ----------

                   1 MTR1 2018-01-01 2018-04-01          1

                   1 MTR1 2018-05-01 2018-06-01          2

          • 2. Re: Non supported SQL92 token is thrown for {0,1} in match_recognize

            Please report suspected bugs to Oracle via your MOS account.

            • 3. Re: Non supported SQL92 token is thrown for {0,1} in match_recognize
              mNem

              Unfortunately, I do not have a MOS account .

              • 4. Re: Non supported SQL92 token is thrown for {0,1} in match_recognize
                mNem

                Just to share more info ...

                 

                Did some further testing and ...

                 

                 

                BANNER                                                                         
                --------------------------------------------------------------------------------
                Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
                PL/SQL Release 12.1.0.2.0 - Production
                CORE 12.1.0.2.0 Production 
                TNS for Linux: Version 12.1.0.2.0 - Production
                NLSRTL Version 12.1.0.2.0 - Production

                 

                VersionResult
                Version 4.0.1.14 Build MAIN-14.48 Passed
                Version 18.1.0.095 Build 095.1630Failed with subject error.

                 

                ----------------

                 

                Additionally, on https://livesql.oracle.com  (Live SQL 18.4.2, running Oracle Database 18c Enterprise Edition - 18.3.0.0.0), it passed the test.