MATCH_RECOGNIZE with CTE in Snowflake

Question

I am using MATCH_RECOGNIZE function in a query with a few CTEs. When I run the query, I got the following error:

SQL compilation error: MATCH_RECOGNIZE not supported in this context.

In my query, there are several CTEs before and after the MATCH_RECOGNIZE partially as below.

WITH cte1 AS (
SELECT *
FROM dataset
WHERE ID IS NOT NULL AND STATUS IS NOT NULL ),

cte2 AS (
SELECT *
FROM cte1
QUALIFY FIRST_VALUE(STATUS) OVER (PARTITION BY ID ORDER BY CREATED_AT) = 'created' )

mr as (
SELECT *
    FROM cte2
    MATCH_RECOGNIZE (
      PARTITION BY ID
      ORDER BY CREATED_AT
      MEASURES MATCH_NUMBER() AS mn,
               MATCH_SEQUENCE_NUMBER AS msn
      ALL ROWS PER MATCH
      PATTERN (c+m+)
      DEFINE
         c AS status='created'
        ,m AS status='missing_info'
        ,p AS status='pending'
    ) m1
    QUALIFY (ROW_NUMBER() OVER(PARTITION BY mn, ID ORDER BY msn) = 1)
          OR(ROW_NUMBER() OVER(PARTITION BY mn, ID ORDER BY msn DESC)=1)
    ORDER BY ID, CREATED_AT ),

cte3 as (
SELECT *
FROM mr
-- some other operations
)

What would be the ideal approach to solve this? e.g. creating a regular view, a materialized view, or a temp table, etc. I tried to create a view but got an error, not sure if it is supported either. How can I use the result of the MATCH_RECOGNIZE in other later CTEs?

When I add the following, it gives this error:

syntax error line xx at position 0 unexpected 'create'.

create view filtered_idents AS

SELECT *
FROM cte2
MATCH_RECOGNIZE (

)

[Using “match_recognize” in a Common Table Expression in Snowflake](https://stackoverflow.com/questions/68183062/using-match-recognize-in-a-common-table-expression-in-snowflake) — Lukasz Szozda, Jul 27 '21 at 13:28
I have checked this post. However, I do have 5 more CTEs after MATCH_RECOGNIZE, so don't think that it will easier and efficient to use subqueries. Is there any alternative? — kimi, Jul 27 '21 at 13:54

score 4 · Accepted Answer · answered Jul 27 '21 at 18:49

This seems to be a non-documented limitation (I asked our awesome docs team to fix this).

In the meantime I could suggest to divide the process into steps to use the match_recognize results.

Reproducing error:

with data as (
    select $1 company, $2 price_date, $3 price
    from values('a',1,10), ('a',2,15)
), cte as (

    select *
    from data match_recognize(
        partition by company
        order by price_date
        measures match_number() as "MATCH_NUMBER"
        all rows per match omit empty matches
        pattern(overavg*)
        define
            overavg as price > avg(price) over (rows between unbounded
                                      preceding and unbounded following)
    )
) 
select * from cte

-- 002362 (0A000): SQL compilation error: MATCH_RECOGNIZE not supported in this context.

2 step solution:

with data as (
    select $1 company, $2 price_date, $3 price
    from values('a',1,10), ('a',2,15)
)

select *
from data match_recognize(
    partition by company
    order by price_date
    measures match_number() as "MATCH_NUMBER"
    all rows per match omit empty matches
    pattern(overavg*)
    define
        overavg as price > avg(price) over (rows between unbounded
                                  preceding and unbounded following)
)
;


with previous_results as (
    select *
    from table(result_scan(last_query_id()))
)

select * 
from previous_results
;

A nice workaround. Could it somehow be wrapped within a view(now we are operating on two separate queries)?Ideally, I hope this limitation will be lifted one day. — Lukasz Szozda, Jul 27 '21 at 19:03

score 0 · Answer 2 · answered Aug 14 '21 at 08:28

Kimi, trying out your snippet I'm getting:

SQL compilation error: syntax error line 11 at position 0 unexpected 'mr'. syntax error line 17 at position 6 unexpected 'MEASURES'.

Line 9 seems to be missing a terminating comma. When I add one and then complete the whole with a simple select statement then I don't get syntax errors anymore, I only get name lookup errors (expected of course).

MATCH_RECOGNIZE with CTE in Snowflake

2 Answers2

Linked