Find broken sequence with match_recognize using Oracle 12C

Viewed 199

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

1 Answers
Related