I want to detect gaps in my CDR_ID sequence for each numbers, for that I am using the match_recognize analytic function.
Code:
SQL> select distinct number,
cdr_id,
status_sequence
from TMP_CDR_GAPS match_recognize (
partition by number
order by cdr_id
measures match_number() status_sequence
all rows per match
after match skip past last row
pattern (section_start in_seq_value*)
define in_seq_value as (cdr_id = prev(cdr_id))
)
This is the result I get :
NUMBER CDR_ID STATUS_SEQUENCE
003301011849 536 1
003301011849 537 2
003301011849 538 3
003301011849 539 4
003301011849 540 5
003301011849 541 6
003301011849 542 7
003301011849 544 8 <-- !!!
003301011849 545 9
003301011849 546 10
003301011849 547 11
003301011849 548 12
You will notice that CDR_ID 543 is missing, this is exactly what I want to detect, so I am expecting the STATUS_SEQUENCE to restart to 1 for the next CDR_ID (CDR_ID 544 in this case).
Can someone help me to understand what is wrong with the match_recognize syntax ?
Thank you upfront, David