sqlpattern-matchingoracle19cmatch-recognize

Dynamic pattern quantifier usage in match_recognize clause

Sample query:

select * from table
match_recognize (
  order by column1
  pattern (anything {**3**,}) 
  define
    anything as column1 = 'col'
);

Considering I have a table and I want to locate consecutive rows which follow the pattern as defined. But for the above example, I am looking for a way to dynamically pass a number instead of passing a static value 3.

I have tried to use bind variable, subquery and nothing seems to work. Is there any approach where I can use the same match recognize query with a dynamic quantifier?

If any non-numeric literal is entered it is throwing this error.
SQL Error: ORA-62501: invalid value specified for bounded quantifier in MATCH_RECOGNIZE clause
62501. 00000 - "invalid value specified for bounded quantifier in MATCH_RECOGNIZE clause"

Oracle version: 19c

Request you to please help me with this if anyone has come across such situation. Thanks in advance.

Solution

  • MATCH_RECOGNIZE does not allow you to use dynamic values in the pattern matching clause; instead you can write the query as:

    SELECT *
FROM   (
  SELECT t.*,
         COUNT(*) OVER () AS num_rows
  FROM   table_name
  WHERE  column1 = 'col'
)
WHERE  num_rows >= :required_rows