Need help using match_recognize in Oracle 21c

Question

I truly hope I can find understaning here as to what is wrong with this code. I have some equipment that send event data as event tags to a database; each timestamped with the time of the equipment of origin.

There is a problem with an equipment's peripheral, and as a consequence these affected equipment sent the relevant event tags, exactly 16 of them, everytime, chronologically ordered. Searching the data manually on the table, it does contain the pattern, coming from different equipment ID.

The purpose is to quantify how many times this problem (pattern) happpens, and on which equipment.

As I was researching what function could help me, I came across Oracle MATCH_RECOGNIZE as a possible tool to obtain a solution. If I am not mistaken, this function uses or emulates regex of some sort (I am not an regex expert). I haven't been able to fine tune the code to show me if it can pick the pattern. I was using inside the PATTERN clause either a '+' (one or more -- plus quantifier), or '*' (zero or more -- star quantifier). If I use +, the result is zilch. If I use * I get data, but there also appears other tags with do not belong to the pattern.

What I would like is to make the code obtain the pattern, with no other event tag.

Below I am sharing the code.

--DROP TABLE TESTPATTERN
SELECT
TO_char(EventDateTime, 'YYYY-MM-DD HH:MM:SS')AS EventDateTime,
EqpID,
EventTag
FROM TESTPATTERN 
MATCH_RECOGNIZE (
 PARTITION BY EqpID
 ORDER BY EventDateTime
    MEASURES
        FIRST(EventDateTime) AS start_date,
        LAST(EventDateTime) AS end_date,
        FIRST(EventTag) AS first_tag,
        LAST(EventTag) AS last_tag
ALL ROWS PER MATCH
AFTER MATCH SKIP PAST LAST ROW 
PATTERN (EventTag1+ EventTag2+ EventTag3+ EventTag4+ EventTag5+ EventTag6+ EventTag7+ EventTag8+ EventTag9+ EventTag10+ EventTag11+ EventTag12+ EventTag13+ EventTag14+ EventTag15+ EventTag16+ EventDateTime1+)
 DEFINE
EventTag1 AS EventTag = 'THOR',
  EventTag2 AS EventTag = 'MJOLNIR',
  EventTag3 AS EventTag = 'LOKI',
  EventTag4 AS EventTag = 'HULK',
  EventTag5 AS EventTag = 'HAWKEYE',
  EventTag6 AS EventTag = 'CAPNAMER',
  EventTag7 AS EventTag = 'FURY',
  EventTag8 AS EventTag = 'STARK',
  EventTag9 AS EventTag = 'FURY',
  EventTag10 AS EventTag = 'STARK',
  EventTag11 AS EventTag = 'THOR',
  EventTag12 AS EventTag = 'MJOLNIR',
  EventTag13 AS EventTag = 'LOKI',
  EventTag14 AS EventTag = 'HULK',
  EventTag15 AS EventTag = 'HAWKEYE',
  EventTag16 AS EventTag = 'CAPNAMER',
  EventDateTime1 as EventDateTime > prev(EventDateTime)
) MR
WHERE 1=1
AND (START_DATE IS NOT NULL OR END_DATE IS NOT NULL OR FIRST_TAG IS NOT NULL OR LAST_TAG IS NOT NULL)
AND (START_DATE IS NOT NULL OR END_DATE IS NOT NULL OR FIRST_TAG IS NOT NULL OR LAST_TAG IS NOT NULL)

Thank you.

UPDATE: I am sharing a sample of the data:

EventDateTime,EqpID,EventTag
1/1/2023,93258,VGBBZX
1/1/2023,97606,IYEOCJ
1/1/2023,84895,BDXWTD
1/1/2023,34603,PYMVRP
1/1/2023,25339,NORDZO
1/1/2023,38960,LXBJTU
1/7/2023,92141,DOHTUC
1/8/2023,69069,HGDCKT
1/9/2023,86781,DBPWTV
1/10/2023,98633,JXBDSU
1/11/2023,56817,RTUJMS
1/12/2023,44871,OIQFTF
1/13/2023,14115,THOR 
1/13/2023,14115,MJOLNIR 
1/13/2023,14115,LOKI 
1/13/2023,14115,HULK 
1/13/2023,14115,HAWKEYE 
1/13/2023,14115,CAPNAMER 
1/13/2023,14115,FURY 
1/13/2023,14115,STARK 
1/13/2023,14115,FURY 
1/13/2023,14115,STARK 
1/13/2023,14115,THOR 
1/13/2023,14115,MJOLNIR 
1/13/2023,14115,LOKI 
1/13/2023,14115,HULK 
1/13/2023,14115,HAWKEYE 
1/13/2023,14115,CAPNAMER 
1/24/2023,36384,CPRBPF
1/24/2023,90629,RKORUN
1/24/2023,82093,NXKCFA
1/24/2023,27730,GPDMUK
2/2/2023,45098,TEHAZM
2/3/2023,55356,EFXAXF
2/4/2023,44523,EDBTGA
2/5/2023,41042,FXPUSS
2/6/2023,34602,AWAPLI
2/7/2023,78860,AMYGPI
2/8/2023,88861,FPLDGU
2/9/2023,13026,YDBZOD
2/10/2023,38968,XDBMYF
2/11/2023,30377,SVLHSA
2/12/2023,20511,IPKOTF
2/13/2023,69469,FDZEFQ
2/14/2023,79857,ZFPBLX
2/15/2023,94276,YGQYTD
2/16/2023,87478,EXINDO
2/17/2023,95831,JBYHTT
2/18/2023,46950,YYPHXY
2/19/2023,56393,CXBJTQ
2/20/2023,70004,JUEQSC
2/21/2023,96613,THOR 
2/21/2023,96613,MJOLNIR 
2/21/2023,96613,LOKI 
2/21/2023,96613,HULK 
2/21/2023,96613,HAWKEYE 
2/21/2023,96613,CAPNAMER 
2/21/2023,96613,FURY 
2/21/2023,96613,STARK 
2/21/2023,96613,FURY 
2/21/2023,96613,STARK 
2/21/2023,96613,THOR 
2/21/2023,96613,MJOLNIR 
2/21/2023,96613,LOKI 
2/21/2023,96613,HULK 
2/21/2023,96613,HAWKEYE 
2/21/2023,96613,CAPNAMER 
3/6/2023,26084,PWNIZZ
3/6/2023,19965,LVMVJH
3/11/2023,69352,WTJJDF
3/12/2023,53016,KACILZ
3/13/2023,33807,SVUNNW
3/14/2023,29156,OSNHOS
3/15/2023,90282,HYNIKV
3/16/2023,23421,HHAZLV
3/17/2023,22600,HTIURE
3/18/2023,46236,PFJLSA
3/19/2023,80520,WYCUNF
3/20/2023,96436,XRZIWB
3/21/2023,48785,RMVTFK
3/22/2023,91108,JQRHHE
3/23/2023,22307,DKMDXC
3/24/2023,24770,YXKMXE
3/25/2023,50685,BXCLEJ
3/26/2023,51084,AKSPTS
3/27/2023,59554,RWIPGQ
3/28/2023,90829,FZVAIE
3/29/2023,49152,QXTITE
3/30/2023,43857,WNPBOI
3/31/2023,81333,KCFBZP
4/1/2023,72786,VHWUEK
4/2/2023,36813,LSETZR
4/3/2023,26868,RFYOJR
4/4/2023,20333,VEOINT
4/5/2023,24696,EPVRVE
4/6/2023,67335,MTZBJG
4/7/2023,97784,WEXDAQ
4/8/2023,76681,OWHZBS
4/9/2023,10958,UECZBX
4/10/2023,75890,QGSMIT
4/11/2023,32046,QXWKYJ
4/12/2023,75267,URAEXB
4/13/2023,76813,ERGNKO
4/14/2023,63003,KHDDRT
4/15/2023,47214,NWNOOQ
4/16/2023,97560,HNIRAO
4/17/2023,96776,MFFYEA
4/18/2023,58917,FHVFHA
4/19/2023,45970,AZRTOP
4/20/2023,60381,MAKSVY
4/21/2023,31555,UEUAWN
4/22/2023,67409,EFKJCI
4/23/2023,95617,EHSETF
4/24/2023,41442,TOTYHJ
4/25/2023,42228,JWTZEA
4/26/2023,37667,WEGYCA
4/27/2023,51275,LQBNED
4/28/2023,16357,QSFTKT
4/29/2023,50233,VOHHXV
4/30/2023,41551,THOGRL
5/1/2023,13884,THOR 
5/1/2023,13884,MJOLNIR 
5/1/2023,13884,LOKI 
5/1/2023,13884,HULK 
5/1/2023,13884,HAWKEYE 
5/1/2023,13884,CAPNAMER 
5/1/2023,13884,FURY 
5/1/2023,13884,STARK 
5/1/2023,13884,FURY 
5/1/2023,13884,STARK 
5/1/2023,13884,THOR 
5/1/2023,13884,MJOLNIR 
5/1/2023,13884,LOKI 
5/1/2023,13884,HULK 
5/1/2023,13884,HAWKEYE 
5/1/2023,13884,CAPNAMER 
5/13/2023,53389,HHKFVV
5/13/2023,40702,IEKFMJ
5/19/2023,14518,FJRSTM
5/20/2023,64307,RJHQHU
5/21/2023,51366,HVIQWR
5/22/2023,86614,LJCONO
5/23/2023,44585,BGIVJO
5/24/2023,86491,MYBBXX
5/25/2023,61505,BZCVJJ
5/25/2023,21615,HXFVNN
5/25/2023,31148,BGPZRY
5/25/2023,15959,MLJPIC
5/25/2023,32447,HOLCSJ
5/25/2023,58595,WIDSVF
5/31/2023,19729,MMSEGW
6/1/2023,60302,AMTOLN
6/2/2023,57501,NTIEJN
6/3/2023,38026,FOGINM
6/4/2023,62404,RYLIWP
6/5/2023,10116,FDIEUK
6/6/2023,60250,JXBUWQ
6/7/2023,56934,CERWGP
6/8/2023,65327,ITUJRG
6/9/2023,24821,YDJLDL
6/10/2023,13959,ZJKUTJ
6/11/2023,81594,EOXQKE
6/12/2023,51408,UIJRNV
6/13/2023,58315,ATWTQD
6/14/2023,38403,YXYGVC
6/15/2023,21385,OCSYMZ
6/16/2023,90186,NSFPIM
6/17/2023,74306,LVDIDX
6/18/2023,34833,FAVCOV
6/19/2023,69123,EYNYSI
6/20/2023,54018,RZDKIQ
6/21/2023,71083,THOR 
6/21/2023,71083,MJOLNIR 
6/21/2023,71083,LOKI 
6/21/2023,71083,HULK 
6/21/2023,71083,HAWKEYE 
6/21/2023,71083,CAPNAMER 
6/21/2023,71083,FURY 
6/21/2023,71083,STARK 
6/21/2023,71083,FURY 
6/21/2023,71083,STARK 
6/21/2023,71083,THOR 
6/21/2023,71083,MJOLNIR 
6/21/2023,71083,LOKI 
6/21/2023,71083,HULK 
6/21/2023,71083,HAWKEYE 
6/21/2023,71083,CAPNAMER 
7/7/2023,15970,VCIZZD
7/8/2023,46295,DUWOAI
7/9/2023,42284,EROYFB
7/10/2023,26038,FJKWGG
7/11/2023,58263,DKEXQJ
7/12/2023,22648,WAHYGN
7/13/2023,76327,HTEEFH
7/14/2023,43199,UZTJAO
7/15/2023,41376,GPVJQM
7/16/2023,48875,RVDYCO
7/17/2023,31696,QXLOUW
7/18/2023,82655,SHCEPV
7/19/2023,21862,ETKIRV
7/20/2023,65806,QOSMWV
7/21/2023,47544,PUXYQK
7/22/2023,61099,ABOMQS
7/23/2023,41702,UMZECW
7/24/2023,48088,TZYAMT
7/25/2023,29845,LTVPVY
7/26/2023,45772,HGFXHX
7/27/2023,76154,PJRRYP
7/28/2023,53096,ZXQGER
7/29/2023,96343,AJBIHA
7/30/2023,61177,CLPPRT
7/31/2023,43500,ZZRYQN
8/1/2023,96134,KUBPUV
8/2/2023,83022,BZJNPK
8/3/2023,79227,TNTMKG
8/4/2023,72617,SGOFXV
8/5/2023,50638,CDFSLR
8/6/2023,35464,NKILHL
8/7/2023,24123,HYHJCD
8/8/2023,21229,LKAGFB
8/9/2023,58962,YYYQZU
8/10/2023,69372,ZROCUL
8/11/2023,50196,QWLURC
8/12/2023,19607,BMWMPU
8/13/2023,63682,XXSSGP
8/14/2023,17084,DZLIGS
8/15/2023,28881,RSENYR
8/16/2023,18611,JPTHKQ
8/17/2023,27402,RXQWOG
8/18/2023,35217,HANFEH
8/19/2023,33933,VQVLFA
8/20/2023,63436,KGVLPN
8/21/2023,86121,IWRBGI
8/22/2023,65971,XSSDJI
8/23/2023,69247,RLRIZC
8/24/2023,77961,FEOFUZ
8/25/2023,47074,AOOCLE
8/26/2023,79347,VCINIP
8/27/2023,24031,NYWQBU
8/28/2023,94265,CQPHKB
8/29/2023,14505,SCFQKY
8/30/2023,81924,CZDOBI
8/31/2023,75484,TQKYZI
9/1/2023,37607,ZOYADT
9/2/2023,30527,WWMHBD
9/3/2023,26774,QYAYWB
9/4/2023,48792,TNJCLB
9/5/2023,72891,EAHYPB
9/6/2023,49372,FFBRGE
9/7/2023,75255,BKYRQA
9/8/2023,17539,FUHMXX
9/9/2023,53570,CAOFII
9/10/2023,22257,THOR 
9/10/2023,22257,MJOLNIR 
9/10/2023,22257,LOKI 
9/10/2023,22257,HULK 
9/10/2023,22257,HAWKEYE 
9/10/2023,22257,CAPNAMER 
9/10/2023,22257,FURY 
9/10/2023,22257,STARK 
9/10/2023,22257,FURY 
9/10/2023,22257,STARK 
9/10/2023,22257,THOR 
9/10/2023,22257,MJOLNIR 
9/10/2023,22257,LOKI 
9/10/2023,22257,HULK 
9/10/2023,22257,HAWKEYE 
9/10/2023,22257,CAPNAMER 
9/26/2023,80826,FIDSRJ
9/27/2023,27732,IZCUFV
9/28/2023,12519,GMQAMR
9/29/2023,30560,GRCOSA
9/30/2023,71680,XUPPST
10/1/2023,78700,HFNKZO
10/2/2023,46046,CEKVIA
10/3/2023,83822,LVEWGO
10/4/2023,89002,WQDDDG
10/5/2023,68492,GGDDSI
10/6/2023,44314,IPSMYN
10/7/2023,17276,IHVLYK
10/8/2023,55134,MJARHY
10/9/2023,70363,VTKFKR

The output should be:

<date-time stamp of row>,<eqipmentID_X>,THOR 
<date-time stamp of row>,<eqipmentID_X>,MJOLNIR 
<date-time stamp of row>,<eqipmentID_X>,LOKI 
<date-time stamp of row>,<eqipmentID_X>,HULK 
<date-time stamp of row>,<eqipmentID_X>,HAWKEYE 
<date-time stamp of row>,<eqipmentID_X>,CAPNAMER 
<date-time stamp of row>,<eqipmentID_X>,FURY 
<date-time stamp of row>,<eqipmentID_X>,STARK 
<date-time stamp of row>,<eqipmentID_X>,FURY 
<date-time stamp of row>,<eqipmentID_X>,STARK 
<date-time stamp of row>,<eqipmentID_X>,THOR 
<date-time stamp of row>,<eqipmentID_X>,MJOLNIR 
<date-time stamp of row>,<eqipmentID_X>,LOKI 
<date-time stamp of row>,<eqipmentID_X>,HULK 
<date-time stamp of row>,<eqipmentID_X>,HAWKEYE 
<date-time stamp of row>,<eqipmentID_X>,CAPNAMER
<date-time stamp of row>,<eqipmentID_Y>,THOR 
<date-time stamp of row>,<eqipmentID_Y>,MJOLNIR 
<date-time stamp of row>,<eqipmentID_Y>,LOKI 
<date-time stamp of row>,<eqipmentID_Y>,HULK 
<date-time stamp of row>,<eqipmentID_Y>,HAWKEYE 
<date-time stamp of row>,<eqipmentID_Y>,CAPNAMER 
<date-time stamp of row>,<eqipmentID_Y>,FURY 
<date-time stamp of row>,<eqipmentID_Y>,STARK 
<date-time stamp of row>,<eqipmentID_Y>,FURY 
<date-time stamp of row>,<eqipmentID_Y>,STARK 
<date-time stamp of row>,<eqipmentID_Y>,THOR 
<date-time stamp of row>,<eqipmentID_Y>,MJOLNIR 
<date-time stamp of row>,<eqipmentID_Y>,LOKI 
<date-time stamp of row>,<eqipmentID_Y>,HULK 
<date-time stamp of row>,<eqipmentID_Y>,HAWKEYE 
<date-time stamp of row>,<eqipmentID_Y>,CAPNAMER 
.
.
.

PD: I have failed to say that those tagnames also occur onother problems with other peripherals of the equipment, in other words, they are not unique to a problem/peripheral.

Please provide your test data and required output – Sayan Malakshinov Jun 26 '23 at 00:24 — Sayan Malakshinov, Jun 26 '23 at 00:24
What is the desired output? – Wernfried Domscheit Jun 26 '23 at 06:29 — Wernfried Domscheit, Jun 26 '23 at 06:29

score 1 · Answer 1 · answered Jun 26 '23 at 08:20

You have two problems:

Your pattern ends with EventTag16+ EventDateTime1+ which matches the 16th term with the EventTag16 definition and then looks for a 17th term matching the EventDateTime1 definition. Your data never has a 17th term so MATCH_RECOGNIZE correctly states that there are no matches to your pattern.
All your EventDateTime values have exactly the same time and will be read from the database file in an effectively random order and the ordered by the query; this is not likely to be the same order that you inserted the data into the database so you are unlikely to match the same pattern.

If you have the data so that it can be read in the correct order (either by having increasing timestamps or another row containing an order) then your query works if you remove that 17th term:

SELECT
TO_char(EventDateTime, 'YYYY-MM-DD HH24:MI:SS')AS EventDateTime,
EqpID,
EventTag
FROM TESTPATTERN 
MATCH_RECOGNIZE (
 PARTITION BY EqpID
 ORDER BY EventDateTime
    MEASURES
        FIRST(EventDateTime) AS start_date,
        LAST(EventDateTime) AS end_date,
        FIRST(EventTag) AS first_tag,
        LAST(EventTag) AS last_tag
ALL ROWS PER MATCH
AFTER MATCH SKIP PAST LAST ROW 
PATTERN (EventTag1+ EventTag2+ EventTag3+ EventTag4+ EventTag5+ EventTag6+ EventTag7+ EventTag8+ EventTag9+ EventTag10+ EventTag11+ EventTag12+ EventTag13+ EventTag14+ EventTag15+ EventTag16+)
 DEFINE
EventTag1 AS EventTag = 'THOR',
  EventTag2 AS EventTag = 'MJOLNIR',
  EventTag3 AS EventTag = 'LOKI',
  EventTag4 AS EventTag = 'HULK',
  EventTag5 AS EventTag = 'HAWKEYE',
  EventTag6 AS EventTag = 'CAPNAMER',
  EventTag7 AS EventTag = 'FURY',
  EventTag8 AS EventTag = 'STARK',
  EventTag9 AS EventTag = 'FURY',
  EventTag10 AS EventTag = 'STARK',
  EventTag11 AS EventTag = 'THOR',
  EventTag12 AS EventTag = 'MJOLNIR',
  EventTag13 AS EventTag = 'LOKI',
  EventTag14 AS EventTag = 'HULK',
  EventTag15 AS EventTag = 'HAWKEYE',
  EventTag16 AS EventTag = 'CAPNAMER'
) MR
WHERE 1=1
AND (START_DATE IS NOT NULL OR END_DATE IS NOT NULL OR FIRST_TAG IS NOT NULL OR LAST_TAG IS NOT NULL)
AND (START_DATE IS NOT NULL OR END_DATE IS NOT NULL OR FIRST_TAG IS NOT NULL OR LAST_TAG IS NOT NULL)

Note: you probably want to format your dates using HH24:MI:SS for the time component (rather than HH which will only show a 12-hour clock, and then you cannot differentiate between AM and PM, and MM which is months, not minutes).

Which for the sample data:

CREATE TABLE testpattern ( EventDateTime,EqpID,EventTag) As
SELECT DATE '2023-01-10', 98633, 'JXBDSU' FROM DUAL UNION ALL
SELECT DATE '2023-01-11', 56817, 'RTUJMS' FROM DUAL UNION ALL
SELECT DATE '2023-01-12', 44871, 'OIQFTF' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '0' SECOND, 14115, 'THOR' FROM DUAL UNION ALL 
SELECT DATE '2023-01-13' + INTERVAL '1' SECOND, 14115, 'MJOLNIR' FROM DUAL UNION ALL 
SELECT DATE '2023-01-13' + INTERVAL '2' SECOND, 14115, 'LOKI' FROM DUAL UNION ALL 
SELECT DATE '2023-01-13' + INTERVAL '3' SECOND, 14115, 'HULK' FROM DUAL UNION ALL 
SELECT DATE '2023-01-13' + INTERVAL '4' SECOND, 14115, 'HAWKEYE' FROM DUAL UNION ALL 
SELECT DATE '2023-01-13' + INTERVAL '5' SECOND, 14115, 'CAPNAMER' FROM DUAL UNION ALL 
SELECT DATE '2023-01-13' + INTERVAL '6' SECOND, 14115, 'FURY' FROM DUAL UNION ALL 
SELECT DATE '2023-01-13' + INTERVAL '7' SECOND, 14115, 'STARK' FROM DUAL UNION ALL 
SELECT DATE '2023-01-13' + INTERVAL '8' SECOND, 14115, 'FURY' FROM DUAL UNION ALL 
SELECT DATE '2023-01-13' + INTERVAL '9' SECOND, 14115, 'STARK' FROM DUAL UNION ALL 
SELECT DATE '2023-01-13' + INTERVAL '10' SECOND, 14115, 'THOR' FROM DUAL UNION ALL 
SELECT DATE '2023-01-13' + INTERVAL '11' SECOND, 14115, 'MJOLNIR' FROM DUAL UNION ALL 
SELECT DATE '2023-01-13' + INTERVAL '12' SECOND, 14115, 'LOKI' FROM DUAL UNION ALL 
SELECT DATE '2023-01-13' + INTERVAL '13' SECOND, 14115, 'HULK' FROM DUAL UNION ALL 
SELECT DATE '2023-01-13' + INTERVAL '14' SECOND, 14115, 'HAWKEYE' FROM DUAL UNION ALL 
SELECT DATE '2023-01-13' + INTERVAL '15' SECOND, 14115, 'CAPNAMER' FROM DUAL UNION ALL 
SELECT DATE '2023-01-24', 36384, 'CPRBPF' FROM DUAL UNION ALL
SELECT DATE '2023-01-24', 90629, 'RKORUN' FROM DUAL UNION ALL
SELECT DATE '2023-01-24', 82093, 'NXKCFA' FROM DUAL;

Outputs:

EVENTDATETIME	EQPID	EVENTTAG
2023-01-13 00:00:00	14115	THOR
2023-01-13 00:00:01	14115	MJOLNIR
2023-01-13 00:00:02	14115	LOKI
2023-01-13 00:00:03	14115	HULK
2023-01-13 00:00:04	14115	HAWKEYE
2023-01-13 00:00:05	14115	CAPNAMER
2023-01-13 00:00:06	14115	FURY
2023-01-13 00:00:07	14115	STARK
2023-01-13 00:00:08	14115	FURY
2023-01-13 00:00:09	14115	STARK
2023-01-13 00:00:10	14115	THOR
2023-01-13 00:00:11	14115	MJOLNIR
2023-01-13 00:00:12	14115	LOKI
2023-01-13 00:00:13	14115	HULK
2023-01-13 00:00:14	14115	HAWKEYE
2023-01-13 00:00:15	14115	CAPNAMER

fiddle

Thanks for responding. As you obviously know, the dataset I have given is not the real one. The real table has timestamp differences to the second. Here's a sample. 22-JUN-23 01.42.19.000000000 AM 22-JUN-23 01.42.18.000000000 AM 22-JUN-23 01.42.17.000000000 AM 22-JUN-23 01.42.17.000000000 AM 22-JUN-23 01.42.16.000000000 AM 22-JUN-23 01.25.11.000000000 AM 22-JUN-23 01.25.10.000000000 AM 22-JUN-23 01.25.10.000000000 AM 22-JUN-23 01.25.09.000000000 AM 22-JUN-23 01.25.09.000000000 AM 22-JUN-23 01.25.08.000000000 AM 22-JUN-23 01.10.16.000000000 AM Will use your format recommendation. — spyker, Jun 26 '23 at 14:54
I Checked your solution with the real data and real tags, it comes out empty. I am going to send a dataset with the real timestamps, but with everything else changed. — spyker, Jun 26 '23 at 14:56

score 0 · Answer 2 · answered Jun 26 '23 at 06:32

0

When you always have exactly this pattern and you just like to count, then simply do

SELECT
    EqpID,
    COUNT(*) / 2 AS Event_COUNT
WHERE EventTag = 'CAPNAMER'
    AND (START_DATE IS NOT NULL OR END_DATE IS NOT NULL OR FIRST_TAG IS NOT NULL OR LAST_TAG IS NOT NULL)
    AND (START_DATE IS NOT NULL OR END_DATE IS NOT NULL OR FIRST_TAG IS NOT NULL OR LAST_TAG IS NOT NULL)
GROUP BY EqpID

EventTag = 'CAPNAMER' appears twice, that's the reason for COUNT(*) / 2. Anything else is not needed according to your question.

answered Jun 26 '23 at 06:32

Wernfried Domscheit

54,457
9
76
110

Thanks for your answer, I have failed to say that those tagnames also occur onother problems with other peripherals of the equipment, in other words, they are not unique to a problem/peripheral. So just counting is not enough. – spyker Jun 26 '23 at 13:08

Need help using match_recognize in Oracle 21c

2 Answers2