8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c Release 1 (12.1)

Introduced in Oracle 8i, Analytic Functions, also known as windowing functions, allow developers to perform tasks in SQL that were previously confined to procedural languages. Oracle 12c has added the MATCH_RECOGNIZE clause into the analytic function syntax to make pattern matching from SQL simpler. This article gives a flavour of what can be done using the MATCH_RECOGNIZE clause, but you will need to refer to the documentation to understand the true level of complexity possible.

Related articles.

Syntax Made Simple

The pattern matching syntax includes a lot of options, which make it quite daunting at first. This section describes a very simplistic view of the syntax, allowing you to take your first steps. For a detailed description of the syntax, see the documentation

Data must be processed correctly and in a deterministic fashion. The PARTITION BY and ORDER BY clauses of all analytic functions are used to break the data up into groups and make sure it is ordered correctly within each group, so order-sensitive analytic functions work as expected. This is explained here. If no partitions are defined, it is assumed the whole result set is one big partition.

PARTITION BY product
ORDER BY tstamp

The MEASURES clause defines the column output that will be produced for each match.

MEASURES  STRT.tstamp AS start_tstamp,
          LAST(UP.tstamp) AS peak_tstamp,
          LAST(DOWN.tstamp) AS end_tstamp

Along with the MEASURES, you need to decide if you want to present all the rows that represent the match, or just summary information.

[ONE ROW | ALL ROWS] PER MATCH

The pattern that represents a match is defined using pattern variables, so it makes sense to look at those first. Pattern variables can use any non-reserved word associated with an expression. Two examples are given below.

DEFINE
  UP AS UP.units_sold > PREV(UP.units_sold),
  FLAT AS FLAT.units_sold = PREV(FLAT.units_sold),
  DOWN AS DOWN.units_sold < PREV(DOWN.units_sold)

DEFINE
  TWINKIES AS TWINKIES.product='TWINKIES',
  DINGDONGS AS DINGDONG.product='DINGDONGS',
  HOHOS AS HOHOS.product='HOHOS'

The pattern is then defined using regular expressions incorporating the pattern variables. Some examples are given below, but a full list of the possibilities is available from the documentation.

-- 1-Many increases, followed by 1-Many decreases in a value. A "V" shaped spike.
PATTERN (STRT UP+ DOWN+)

-- 1-Many increases, followed by a single decrease, then 1-Many increases. A single dip, during the rise.
PATTERN (STRT UP+ DOWN{1} UP+)

-- 1-5 Twinkies, followed by 1 DingDong, followed by 2 HoHos.
PATTERN(STRT TWINKIES{1,5} DINGDONGS{1} HOHOS{2})

The AFTER MATCH SKIP clause defines where the search is restarted from. Available options include the following.

There are a number of functions that provide additional information about the displayed output.

Navigation around the rows in a patterns is possible using the PREV, NEXT, FIRST and LAST functions.

PREV(UP.units_sold)     -- Value of units_sold from previous row.

PREV(UP.units_sold, 2)  -- Value of units_sold from the row before the previous row (offset of 2 rows).

NEXT(UP.units_sold)     -- Value of units_sold from the next row.

NEXT(UP.units_sold, 2)  -- Value of units_sold from the row after the following row (offset of 2 rows).

FIRST(UP.units_sold)    -- First row in the pattern.

FIRST(UP.units_sold, 1) -- Row following the first row (offset of 1 row).

LAST(UP.units_sold)     -- Last row in the pattern.

LAST(UP.units_sold, 1)  -- Row preceding the last row (offset of 1 row).

The pattern navigation, along with aggregate functions, can be qualified with the FINAL and RUNNING semantics keywords. These are effectively a windowing clause within the pattern, defining if the action relates to the whole pattern, or from the start of the pattern to the current row.

Setup

The examples in this article require the following two tables. The first table defines the total sales of each product per day. In this case, there is only data for a single product.

DROP TABLE sales_history PURGE;

CREATE TABLE sales_history (
  id            NUMBER,
  product       VARCHAR2(20),
  tstamp        TIMESTAMP,
  units_sold    NUMBER,
  CONSTRAINT sales_history_pk PRIMARY KEY (id)
);

ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';

INSERT INTO sales_history VALUES ( 1, 'TWINKIES', '01-OCT-2014', 17);
INSERT INTO sales_history VALUES ( 2, 'TWINKIES', '02-OCT-2014', 19);
INSERT INTO sales_history VALUES ( 3, 'TWINKIES', '03-OCT-2014', 23);
INSERT INTO sales_history VALUES ( 4, 'TWINKIES', '04-OCT-2014', 23);
INSERT INTO sales_history VALUES ( 5, 'TWINKIES', '05-OCT-2014', 16);
INSERT INTO sales_history VALUES ( 6, 'TWINKIES', '06-OCT-2014', 10);
INSERT INTO sales_history VALUES ( 7, 'TWINKIES', '07-OCT-2014', 14);
INSERT INTO sales_history VALUES ( 8, 'TWINKIES', '08-OCT-2014', 16);
INSERT INTO sales_history VALUES ( 9, 'TWINKIES', '09-OCT-2014', 15);
INSERT INTO sales_history VALUES (10, 'TWINKIES', '10-OCT-2014', 17);
INSERT INTO sales_history VALUES (11, 'TWINKIES', '11-OCT-2014', 23);
INSERT INTO sales_history VALUES (12, 'TWINKIES', '12-OCT-2014', 30);
INSERT INTO sales_history VALUES (13, 'TWINKIES', '13-OCT-2014', 31);
INSERT INTO sales_history VALUES (14, 'TWINKIES', '14-OCT-2014', 29);
INSERT INTO sales_history VALUES (15, 'TWINKIES', '15-OCT-2014', 25);
INSERT INTO sales_history VALUES (16, 'TWINKIES', '16-OCT-2014', 21);
INSERT INTO sales_history VALUES (17, 'TWINKIES', '17-OCT-2014', 35);
INSERT INTO sales_history VALUES (18, 'TWINKIES', '18-OCT-2014', 46);
INSERT INTO sales_history VALUES (19, 'TWINKIES', '19-OCT-2014', 45);
INSERT INTO sales_history VALUES (20, 'TWINKIES', '20-OCT-2014', 30);
COMMIT;

The following query shows the pattern of the data, which we will refer to later.

ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';

SET PAGESIZE 50
COLUMN product    FORMAT A10
COLUMN tstamp     FORMAT A11
COLUMN graph      FORMAT A50

SELECT id,
       product,
       tstamp,
       units_sold,
       RPAD('#', units_sold, '#') AS graph
FROM   sales_history
ORDER BY id;

        ID PRODUCT    TSTAMP      UNITS_SOLD GRAPH
---------- ---------- ----------- ---------- --------------------------------------------------
         1 TWINKIES   01-OCT-2014         17 #################
         2 TWINKIES   02-OCT-2014         19 ###################
         3 TWINKIES   03-OCT-2014         23 #######################
         4 TWINKIES   04-OCT-2014         23 #######################
         5 TWINKIES   05-OCT-2014         16 ################
         6 TWINKIES   06-OCT-2014         10 ##########
         7 TWINKIES   07-OCT-2014         14 ##############
         8 TWINKIES   08-OCT-2014         16 ################
         9 TWINKIES   09-OCT-2014         15 ###############
        10 TWINKIES   10-OCT-2014         17 #################
        11 TWINKIES   11-OCT-2014         23 #######################
        12 TWINKIES   12-OCT-2014         30 ##############################
        13 TWINKIES   13-OCT-2014         31 ###############################
        14 TWINKIES   14-OCT-2014         29 #############################
        15 TWINKIES   15-OCT-2014         25 #########################
        16 TWINKIES   16-OCT-2014         21 #####################
        17 TWINKIES   17-OCT-2014         35 ###################################
        18 TWINKIES   18-OCT-2014         46 ##############################################
        19 TWINKIES   19-OCT-2014         45 #############################################
        20 TWINKIES   20-OCT-2014         30 ##############################

20 rows selected.

SQL>

The following table defines an audit trail of all sales as they happen.

DROP TABLE sales_audit PURGE;

CREATE TABLE sales_audit (
  id            NUMBER,
  product       VARCHAR2(20),
  tstamp        TIMESTAMP,
  CONSTRAINT sales_audit_pk PRIMARY KEY (id)
);

ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS';

INSERT INTO sales_audit VALUES ( 1, 'TWINKIES', '01-OCT-2014 12:00:01');
INSERT INTO sales_audit VALUES ( 2, 'TWINKIES', '01-OCT-2014 12:00:02');
INSERT INTO sales_audit VALUES ( 3, 'DINGDONGS', '01-OCT-2014 12:00:03');
INSERT INTO sales_audit VALUES ( 4, 'HOHOS', '01-OCT-2014 12:00:04');
INSERT INTO sales_audit VALUES ( 5, 'HOHOS', '01-OCT-2014 12:00:05');
INSERT INTO sales_audit VALUES ( 6, 'TWINKIES', '01-OCT-2014 12:00:06');
INSERT INTO sales_audit VALUES ( 7, 'TWINKIES', '01-OCT-2014 12:00:07');
INSERT INTO sales_audit VALUES ( 8, 'DINGDONGS', '01-OCT-2014 12:00:08');
INSERT INTO sales_audit VALUES ( 9, 'DINGDONGS', '01-OCT-2014 12:00:09');
INSERT INTO sales_audit VALUES (10, 'HOHOS', '01-OCT-2014 12:00:10');
INSERT INTO sales_audit VALUES (11, 'HOHOS', '01-OCT-2014 12:00:11');
INSERT INTO sales_audit VALUES (12, 'TWINKIES', '01-OCT-2014 12:00:12');
INSERT INTO sales_audit VALUES (13, 'TWINKIES', '01-OCT-2014 12:00:13');
INSERT INTO sales_audit VALUES (14, 'DINGDONGS', '01-OCT-2014 12:00:14');
INSERT INTO sales_audit VALUES (15, 'DINGDONGS', '01-OCT-2014 12:00:15');
INSERT INTO sales_audit VALUES (16, 'HOHOS', '01-OCT-2014 12:00:16');
INSERT INTO sales_audit VALUES (17, 'TWINKIES', '01-OCT-2014 12:00:17');
INSERT INTO sales_audit VALUES (18, 'TWINKIES', '01-OCT-2014 12:00:18');
INSERT INTO sales_audit VALUES (19, 'TWINKIES', '01-OCT-2014 12:00:19');
INSERT INTO sales_audit VALUES (20, 'TWINKIES', '01-OCT-2014 12:00:20');
COMMIT;

The following query shows the order of the product sales for a specific time period, which we will refer to later.

COLUMN tstamp FORMAT A20

SELECT *
FROM   sales_audit
ORDER BY tstamp;

        ID PRODUCT    TSTAMP
---------- ---------- --------------------
         1 TWINKIES   01-OCT-2014 12:00:01
         2 TWINKIES   01-OCT-2014 12:00:02
         3 DINGDONG   01-OCT-2014 12:00:03
         4 HOHOS      01-OCT-2014 12:00:04
         5 HOHOS      01-OCT-2014 12:00:05
         6 TWINKIES   01-OCT-2014 12:00:06
         7 TWINKIES   01-OCT-2014 12:00:07
         8 DINGDONGS  01-OCT-2014 12:00:08
         9 DINGDONGS  01-OCT-2014 12:00:09
        10 HOHOS      01-OCT-2014 12:00:10
        11 HOHOS      01-OCT-2014 12:00:11
        12 TWINKIES   01-OCT-2014 12:00:12
        13 TWINKIES   01-OCT-2014 12:00:13
        14 DINDONGS   01-OCT-2014 12:00:14
        15 HOHOS      01-OCT-2014 12:00:15
        16 TWINKIES   01-OCT-2014 12:00:16
        17 TWINKIES   01-OCT-2014 12:00:17
        18 TWINKIES   01-OCT-2014 12:00:18
        19 TWINKIES   01-OCT-2014 12:00:19
        20 TWINKIES   01-OCT-2014 12:00:20

20 rows selected.

SQL>

Examples

Check for peaks/spikes in sales, where sales go up then down. Notice the pattern variables "UP", "FLAT" and "DOWN" are defined to show an increase, no change and decrease in the value respectively. The pattern we are searching for is 1-Many UPs, optionally leveling off, followed by 1-Many Downs. The measures displayed are the start of the pattern (STRT.tstamp), the top of the peak (LAST(UP.tstamp)) and the bottom of the drop (LAST(DOWN.tstamp)), with a single row for each match. We are also displaying the MATCH_NUMBER().

ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';

COLUMN start_tstamp FORMAT A11
COLUMN peak_tstamp  FORMAT A11
COLUMN end_tstamp   FORMAT A11

SELECT *
FROM   sales_history MATCH_RECOGNIZE (
         PARTITION BY product
         ORDER BY tstamp
         MEASURES  STRT.tstamp AS start_tstamp,
                   LAST(UP.tstamp) AS peak_tstamp,
                   LAST(DOWN.tstamp) AS end_tstamp,
                   MATCH_NUMBER() AS mno
         ONE ROW PER MATCH
         AFTER MATCH SKIP TO LAST DOWN
         PATTERN (STRT UP+ FLAT* DOWN+)
         DEFINE
           UP AS UP.units_sold > PREV(UP.units_sold),
           FLAT AS FLAT.units_sold = PREV(FLAT.units_sold),
           DOWN AS DOWN.units_sold < PREV(DOWN.units_sold)
       ) MR
ORDER BY MR.product, MR.start_tstamp;

PRODUCT    START_TSTAM PEAK_TSTAMP END_TSTAMP         MNO
---------- ----------- ----------- ----------- ----------
TWINKIES   01-OCT-2014 03-OCT-2014 06-OCT-2014          1
TWINKIES   06-OCT-2014 08-OCT-2014 09-OCT-2014          2
TWINKIES   09-OCT-2014 13-OCT-2014 16-OCT-2014          3
TWINKIES   16-OCT-2014 18-OCT-2014 20-OCT-2014          4

4 rows selected.

SQL>

The output tells us there were 4 distinct peaks/spikes in the sales, giving us the location of the start, peak and end of the pattern.

The following query is similar, but shows all the rows for the match and includes the CLASSIFIER() function to indicate which pattern variable is relevant for each row.

SET LINESIZE 110
ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';

COLUMN start_tstamp FORMAT A11
COLUMN peak_tstamp  FORMAT A11
COLUMN end_tstamp   FORMAT A11
COLUMN cls          FORMAT A5

SELECT *
FROM   sales_history MATCH_RECOGNIZE (
         PARTITION BY product
         ORDER BY tstamp
         MEASURES  STRT.tstamp AS start_tstamp,
                   FINAL LAST(UP.tstamp) AS peak_tstamp,
                   FINAL LAST(DOWN.tstamp) AS end_tstamp,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cls
         ALL ROWS PER MATCH
         AFTER MATCH SKIP TO LAST DOWN
         PATTERN (STRT UP+ FLAT* DOWN+)
         DEFINE
           UP AS UP.units_sold > PREV(UP.units_sold),
           DOWN AS DOWN.units_sold < PREV(DOWN.units_sold),
           FLAT AS FLAT.units_sold = PREV(FLAT.units_sold)
       ) MR
ORDER BY MR.product, MR.mno, MR.tstamp;

PRODUCT    TSTAMP      START_TSTAM PEAK_TSTAMP END_TSTAMP         MNO CLS           ID UNITS_SOLD
---------- ----------- ----------- ----------- ----------- ---------- ----- ---------- ----------
TWINKIES   01-OCT-2014 01-OCT-2014 03-OCT-2014 06-OCT-2014          1 STRT           1         17
TWINKIES   02-OCT-2014 01-OCT-2014 03-OCT-2014 06-OCT-2014          1 UP             2         19
TWINKIES   03-OCT-2014 01-OCT-2014 03-OCT-2014 06-OCT-2014          1 UP             3         23
TWINKIES   04-OCT-2014 01-OCT-2014 03-OCT-2014 06-OCT-2014          1 FLAT           4         23
TWINKIES   05-OCT-2014 01-OCT-2014 03-OCT-2014 06-OCT-2014          1 DOWN           5         16
TWINKIES   06-OCT-2014 01-OCT-2014 03-OCT-2014 06-OCT-2014          1 DOWN           6         10
TWINKIES   06-OCT-2014 06-OCT-2014 08-OCT-2014 09-OCT-2014          2 STRT           6         10
TWINKIES   07-OCT-2014 06-OCT-2014 08-OCT-2014 09-OCT-2014          2 UP             7         14
TWINKIES   08-OCT-2014 06-OCT-2014 08-OCT-2014 09-OCT-2014          2 UP             8         16
TWINKIES   09-OCT-2014 06-OCT-2014 08-OCT-2014 09-OCT-2014          2 DOWN           9         15
TWINKIES   09-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014          3 STRT           9         15
TWINKIES   10-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014          3 UP            10         17
TWINKIES   11-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014          3 UP            11         23
TWINKIES   12-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014          3 UP            12         30
TWINKIES   13-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014          3 UP            13         31
TWINKIES   14-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014          3 DOWN          14         29
TWINKIES   15-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014          3 DOWN          15         25
TWINKIES   16-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014          3 DOWN          16         21
TWINKIES   16-OCT-2014 16-OCT-2014 18-OCT-2014 20-OCT-2014          4 STRT          16         21
TWINKIES   17-OCT-2014 16-OCT-2014 18-OCT-2014 20-OCT-2014          4 UP            17         35
TWINKIES   18-OCT-2014 16-OCT-2014 18-OCT-2014 20-OCT-2014          4 UP            18         46
TWINKIES   19-OCT-2014 16-OCT-2014 18-OCT-2014 20-OCT-2014          4 DOWN          19         45
TWINKIES   20-OCT-2014 16-OCT-2014 18-OCT-2014 20-OCT-2014          4 DOWN          20         30

23 rows selected.

SQL>

Notice how some rows are duplicated, as they represent the end of one pattern and the start of the next.

The next example identified the only occurrence of a general rise in values, containing a single dipping value.

SELECT *
FROM   sales_history MATCH_RECOGNIZE (
         PARTITION BY product
         ORDER BY tstamp
         MEASURES  STRT.tstamp AS start_tstamp,
                   FINAL LAST(UP.tstamp) AS peak_tstamp,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cls
         ALL ROWS PER MATCH
         AFTER MATCH SKIP TO LAST DOWN
         PATTERN (STRT UP+ DOWN{1} UP+)
         DEFINE
           UP AS UP.units_sold > PREV(UP.units_sold),
           DOWN AS DOWN.units_sold < PREV(DOWN.units_sold)
       ) MR
ORDER BY MR.product, MR.tstamp;

PRODUCT    TSTAMP      START_TSTAM PEAK_TSTAMP        MNO CLS           ID UNITS_SOLD
---------- ----------- ----------- ----------- ---------- ----- ---------- ----------
TWINKIES   06-OCT-2014 06-OCT-2014 13-OCT-2014          1 STRT           6         10
TWINKIES   07-OCT-2014 06-OCT-2014 13-OCT-2014          1 UP             7         14
TWINKIES   08-OCT-2014 06-OCT-2014 13-OCT-2014          1 UP             8         16
TWINKIES   09-OCT-2014 06-OCT-2014 13-OCT-2014          1 DOWN           9         15
TWINKIES   10-OCT-2014 06-OCT-2014 13-OCT-2014          1 UP            10         17
TWINKIES   11-OCT-2014 06-OCT-2014 13-OCT-2014          1 UP            11         23
TWINKIES   12-OCT-2014 06-OCT-2014 13-OCT-2014          1 UP            12         30
TWINKIES   13-OCT-2014 06-OCT-2014 13-OCT-2014          1 UP            13         31

8 rows selected.

SQL>

Turning to the audit table, we can look for specific patterns of sales. In this case, we don't want to partition the result set as the pattern spans multiple products. We are looking for sales of 2-Many TWINKIES, followed by a 2 DINGDONGS and a 1 HOHOS sale, followed by 3-Many TWINKIES sales.

ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS';

COLUMN tstamp       FORMAT A20
COLUMN start_tstamp FORMAT A20
COLUMN end_tstamp   FORMAT A20
COLUMN cls          FORMAT A10

SELECT *
FROM   sales_audit MATCH_RECOGNIZE (
         --PARTITION BY product
         ORDER BY tstamp
         MEASURES  FIRST(TWINKIES.tstamp) AS start_tstamp,
                   FINAL LAST(TWINKIES.tstamp) AS end_tstamp,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cls
         ALL ROWS PER MATCH
         AFTER MATCH SKIP TO LAST TWINKIES
         PATTERN(TWINKIES{2,} DINGDONGS{2} HOHOS{1} TWINKIES{3,})
         DEFINE
           TWINKIES AS TWINKIES.product='TWINKIES',
           DINGDONGS AS DINGDONGS.product='DINGDONGS',
           HOHOS AS HOHOS.product='HOHOS'
       ) MR
ORDER BY MR.mno, MR.tstamp;

TSTAMP               START_TSTAMP         END_TSTAMP                  MNO CLS                ID PRODUCT
-------------------- -------------------- -------------------- ---------- ---------- ---------- ----------
01-OCT-2014 12:00:12 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          1 TWINKIES           12 TWINKIES
01-OCT-2014 12:00:13 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          1 TWINKIES           13 TWINKIES
01-OCT-2014 12:00:14 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          1 DINGDONGS          14 DINGDONGS
01-OCT-2014 12:00:15 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          1 DINGDONGS          15 DINGDONGS
01-OCT-2014 12:00:16 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          1 HOHOS              16 HOHOS
01-OCT-2014 12:00:17 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          1 TWINKIES           17 TWINKIES
01-OCT-2014 12:00:18 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          1 TWINKIES           18 TWINKIES
01-OCT-2014 12:00:19 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          1 TWINKIES           19 TWINKIES
01-OCT-2014 12:00:20 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          1 TWINKIES           20 TWINKIES

9 rows selected.

SQL>

We can see there is only a single match for that pattern in the data.

Next we check for a run of TWINKIES sales separated by exactly three sales matching any combination of DINGDONGS and/or HOHOS.

SELECT *
FROM   sales_audit MATCH_RECOGNIZE (
         --PARTITION BY product
         ORDER BY tstamp
         MEASURES  FIRST(TWINKIES.tstamp) AS start_tstamp,
                   FINAL LAST(TWINKIES.tstamp) AS end_tstamp,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cls
         ALL ROWS PER MATCH
         AFTER MATCH SKIP TO LAST TWINKIES
         PATTERN(TWINKIES+ (DINGDONGS | HOHOS){3} TWINKIES+)
         DEFINE
           TWINKIES AS TWINKIES.product='TWINKIES',
           DINGDONGS AS DINGDONGS.product='DINGDONGS',
           HOHOS AS HOHOS.product='HOHOS'
       ) MR
ORDER BY MR.mno, MR.tstamp;

TSTAMP               START_TSTAMP         END_TSTAMP                  MNO CLS                ID PRODUCT
-------------------- -------------------- -------------------- ---------- ---------- ---------- ----------
01-OCT-2014 12:00:01 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07          1 TWINKIES            1 TWINKIES
01-OCT-2014 12:00:02 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07          1 TWINKIES            2 TWINKIES
01-OCT-2014 12:00:03 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07          1 DINGDONGS           3 DINGDONGS
01-OCT-2014 12:00:04 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07          1 HOHOS               4 HOHOS
01-OCT-2014 12:00:05 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07          1 HOHOS               5 HOHOS
01-OCT-2014 12:00:06 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07          1 TWINKIES            6 TWINKIES
01-OCT-2014 12:00:07 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07          1 TWINKIES            7 TWINKIES
01-OCT-2014 12:00:12 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          2 TWINKIES           12 TWINKIES
01-OCT-2014 12:00:13 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          2 TWINKIES           13 TWINKIES
01-OCT-2014 12:00:14 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          2 DINGDONGS          14 DINGDONGS
01-OCT-2014 12:00:15 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          2 DINGDONGS          15 DINGDONGS
01-OCT-2014 12:00:16 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          2 HOHOS              16 HOHOS
01-OCT-2014 12:00:17 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          2 TWINKIES           17 TWINKIES
01-OCT-2014 12:00:18 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          2 TWINKIES           18 TWINKIES
01-OCT-2014 12:00:19 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          2 TWINKIES           19 TWINKIES
01-OCT-2014 12:00:20 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          2 TWINKIES           20 TWINKIES

16 rows selected.

SQL>

We can see there are two matches to this pattern.

Once you get a feeling for the pattern matching syntax, you should be able to start building your own examples for patterns. You can also check out the edocumentation for more examples.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.