Insert into first and last row of a MATCH_RECOGNIZE select Oracle

Question

I am not really good in sql and i need your help.

I got a table with sample data in Oracle DB like this:

| DATE_TIME                 | TYPE | LAT   | LON   | MSG   |
| :------------------------ | :--- | :---- | :---- | :---- |
| 12.11.20 10:04.00.0000000 | A    | 10.03 | 11.15 |       |
| 12.11.20 10:05.00.0000000 | A    | 10.04 | 11.14 |       |
| 12.11.20 10:06.00.0000000 | A    | 10.05 | 11.13 |       |
| 12.11.20 11:18.00.0000000 | B    | 20.45 | 40.58 |       |
| 12.11.20 11:19.00.0000000 | B    | 20.46 | 40.59 |       |
| 12.11.20 11:20.00.0000000 | B    | 20.47 | 40.60 |       |

I have a select which gives me the START_TIME, END_TIME, TYPE, START_LAT, START_LON, END_LAT, END_LON, START_MSG, END_MSG:

SELECT 
  TO_CHAR(START_TIME, 'DD.MM.YYYY HH24:MI') AS START_TIME,
  TO_CHAR(END_TIME, 'DD.MM.YYYY HH24:MI') AS END_TIME,
  TYPE,
  START_LAT,
  START_LON,
  END_LAT,
  END_LON,
  START_MSG,
  END_MSG
FROM TABLE_1
  MATCH_RECOGNIZE (
    PARTITION BY TYPE
    ORDER BY DATE_TIME
    MEASURES
      FIRST(DATE_TIME) AS START_TIME,
      LAST(DATE_TIME) AS END_TIME,
      FIRST(LAT) AS START_LAT,
      FIRST(LON) AS START_LON,
      LAST(LAT) AS END_LAT,
      LAST(LON) AS END_LON,
      FIRST(MSG) AS START_MSG,
      LAST(MSG) AS END_MSG
    ONE ROW PER MATCH
    PATTERN ( adjacent_minutes * last_minute )
    DEFINE adjacent_minutes AS NEXT(DATE_TIME) <= LAST(DATE_TIME) + INTERVAL '5' MINUTE
  );

Output of this select:

| START_TIME     | END_TIME       | TYPE | START_LAT | START_LON | END_LAT | END_LON | START_MSG | END_MSG |
| :------------- | :------------- | :--- | :-------- | :-------- | :------ | :------ | :-------- | :------ |
| 12.11.20 10:04 | 12.11.20 10:06 | A    | 10.03     | 11.15     | 10.05   | 11.13   |           |         |
| 12.11.20 11:18 | 12.11.20 11:20 | B    | 20.45     | 40.58     | 20.47   | 40.60   |           |         |

Now I need to insert data with value "country xy" into the column START_MSG when for example START_LAT is between 10 and 11 and START_LON is between 11 and 12. Also the same for END_MSG.

How can I do that?

Sorry for my bad English skills ;).

Thank you so much in advance!

score 0 · Accepted Answer · answered Dec 16 '20 at 11:03

You can use CASE WHEN expression as follows:

SELECT ...
  ....
  END_LAT,
  END_LON,
  CASE WHEN START_LAT BETWEEN 10 AND 11 AND START_LON BETWEEN 11 AND 12 
       THEN 'country xy' 
       ELSE START_MSG 
  END AS START_MSG,
  CASE WHEN START_LAT BETWEEN 10 AND 11 AND START_LON BETWEEN 11 AND 12 
       THEN 'country xy' 
       ELSE END_MSG 
  END AS END_MSG
FROM TABLE_1
  MATCH_RECOGNIZE (
    ...

OR use your entire query in the sub-query and in the outer query use this CASE..WHEN for these two columns

Insert into first and last row of a MATCH_RECOGNIZE select Oracle

1 Answers1