Oracle 12c Row Pattern Matching: Beat the Best Pre-12c Solutions at OpenWorld!

Catchy title, don’t you think? My session has been moved to Monday 4 P.M., in direct conflict with Tom Kyte – and Keith Laker, who asked me to present in the first place.

Avoid the lines: come see the MATCH_RECOGNIZE clause push great pre-12c solutions into retirement. As a bonus, be the first person on your block able to prevent “catastrophic backtracking”.

Click here to see my session in the OpenWorld catalog

Advertisements

4 thoughts on “Oracle 12c Row Pattern Matching: Beat the Best Pre-12c Solutions at OpenWorld!

      • Stew – Thanks for your help. just slowly learning on Pattern matching, may be one day i think i can master on it. I think you should be part of Asktom Answers team, see i have been waiting for them to answer, but no response. but you did it spontaneously. Thanks.

        Could you also help me to answer this question, i asked at asktom.oracle.com ?

        I was expecting grp3=10 for the second row in the result set, which is perfect. but why don’t i see grp3=40 for the Eighth row in this result ? please help me to understand – what am i missing here.

        rajesh@ORA12C> select *
        2 from transactions
        3 match_recognize(
        4 partition by customer_id
        5 order by trans_date
        6 measures
        7 case
        8 when prev(trans_type) in (‘CASH’,’CREDIT_CARD’) and
        9 trans_type = ‘REDEMPTION’ then prev(txn_id)
        10 when prev(trans_type) = trans_type then first(txn_id)
        11 when prev(trans_type) = ‘REDEMPTION’ and
        12 trans_type in (‘CASH’,’CREDIT_CARD’) then prev(txn_id)
        13 end grp ,
        14 prev(trans_type) as prev_trans_type,
        15 trans_type as curr_trans_type,
        16 case when prev(trans_type) = ‘CREDIT_CARD’ and
        17 trans_type = ‘REDEMPTION’ then prev(txn_id) end grp3
        18 all rows per match with unmatched rows
        19 after match skip past last row
        20 pattern( cash redemption+ | credit redemption+ | redemption cash+ | redemption credit+ )
        21 define
        22 cash as cash.trans_type =’CASH’ ,
        23 credit as credit.trans_type=’CREDIT_CARD’,
        24 redemption as redemption.trans_type=’REDEMPTION’ )
        25 /

        CUSTOMER_ID TRANS_DATE GRP PREV_TRANS_TY CURR_TRANS_TY GRP3 TXN_ID TRANS_TYPE
        ———– ———– ———- ————- ————- ———- ———- ————
        444 01-MAR-2013 CREDIT_CARD 10 CREDIT_CARD
        444 01-AUG-2014 10 CREDIT_CARD REDEMPTION 10 2 REDEMPTION
        444 01-NOV-2014 10 REDEMPTION REDEMPTION 3 REDEMPTION
        444 01-OCT-2015 3 REDEMPTION CASH 11 CASH
        444 01-DEC-2015 11 CASH REDEMPTION 17 REDEMPTION
        999 01-JAN-2014 50
        999 01-FEB-2014 CASH CREDIT_CARD 40 CREDIT_CARD
        999 01-JAN-2015 CREDIT_CARD REDEMPTION 20 REDEMPTION
        999 01-JUN-2015 REDEMPTION REDEMPTION 21 REDEMPTION
        999 01-DEC-2015 REDEMPTION REDEMPTION 22 REDEMPTION

        10 rows selected.

        rajesh@ORA12C>

  1. Rajeshwaran,

    In the MEASURES clause, add two columns:

    classifier() cl, match_number() mn

    They will help you see what is going on.

    Sometimes a row is needed twice: once as the “current” row and once as the row that contributes the GRP3 value. Once you get a match on the “current” row you skip past it, so that row is gone and it cannot contribute a GRP3 value. Also, the contributing row is not necessarily the PREVious row.

    Why not start over by learning why my method works?

    Best regards, Stew

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s