1 Reply Latest reply on Jun 5, 2018 7:53 AM by mNem

    Match_recognize clause (pattern) does not accept quotation mark in sql-developer 17.4.1.054 /18.1.0.095

    1616425

      Hi,

       

      I tested the match_recognize clause in Oracle sql-deveoper 17.4.1.054 and in the 18.1.0.095.

      Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

       

      Match_regocognize is not recognized by the Code Outline

      and using the quotation mark '?' in the pattern results in an error.

       

      create table mr_tabel (part varchar2(1), letter varchar2(1) ,num number );
      insert into mr_tabel (part, letter, num) values('a','a',1);
      insert into mr_tabel (part, letter, num) values('a','b',2);
      commit;
      select mr.* from mr_tabel
      MATCH_RECOGNIZE
      (
       partition by part order by num
       measures MATCH_NUMBER() AS mno
              , CLASSIFIER() AS cls
       ALL ROWS PER MATCH WITH UNMATCHED ROWS
       pattern (a? b )
       define a as letter = 'a'
            , b as letter = 'b'
      ) mr;
      

       

      result in an error:( Dutch: Parameter IN of UIT ontbreekt bij index: 1)     (translated :  Parameter IN or OUT is missing by index: 1 )

      This query is running fine in other sql-tools. When changing the '?'  in a plus-sign '+' it wil run. But it is still not being recognized by the code outline viewer: Syntax err; Partial parse tree: