I have a table as following
ORDER_ID | STATUS | STATUS_DATE |
---|---|---|
111 | allocated | 2024-12-01 |
111 | approved | 2024-12-02 |
111 | fulfilled | 2024-12-03 |
222 | allocated | 2024-12-02 |
222 | allocated | 2024-12-03 |
222 | approved | 2024-12-04 |
I would like to identity the orders that have been allocated
and approved
but not fulfilled yet along with the status_date
for each of the status. I am using the following match_recognize
query as following:
select * from (
select *
from order_status
match_recognize (
partition by order_id
order by status_date asc
measures
first(status_date) as status_date
all rows PER MATCH
PATTERN (ALLOCATED+ APPROVED+$)
DEFINE
ALLOCATED as status = 'allocated'
, APPROVED as status = 'approved'
, FULFILLED as status = 'fulfilled'
)
)
pivot (min(status_date) for status in (ANY));
But I am getting the following error message:
Error: OVER clause not allowed in MATCH_RECOGNIZE MEASURES clause.