Oracle Sequential actions

Question

I have a table of data detail the action sequence of a row on a parent table where column ID is that foreign key. Column SEQ is the order in which those actions took place and ACTION is what took place.

ID          SEQ ACTION
12345.00    2   SUSPEND
12345.00    3   CLEAR
12345.00    4   SUSPEND
12345.00    6   CLEAR
12345.00    7   SUSPEND
12345.00    8   RESUME
12345.00    9   SUSPEND
12345.00    10  RESUME
12345.00    11  CLEAR

I am trying to present the data in such a way that I can identify SUSPEND and RESUME actions that were not cleared. In this scenario my result would look like this;

12345.00    7   SUSPEND    8    RESUME
12345.00    9   SUSPEND

This is the case because;

SUSPEND action 2 was removed by CLEAR action 3.

SUSPEND action 4 was removed by CLEAR action 6.

RESUME action 8 was removed by CLEAR action 9.

The ACTION column can have other actions in the sequence so I have removed those for clarity.

An action is cleared if it will be succeeded by a CLEAR.

Sorry if this is confusing. I cannot change the schema!

I have tried to simplify the question;

ID          SEQ ACTION
12345.00    2   SUSPEND
12345.00    3   RESUME
12345.00    4   CLEAR
12345.00    5   RESUME
12345.00    6   SUSPEND

The result should look like this;

12345.00    2   SUSPEND   5 RESUME
12345.00    6   SUSPEND

I have tried a couple of approaches but I just cannot figure how to stop the RESUME at number 3 being included.

Answer 1

Try:

SELECT *
FROM (
   SELECT t.*,
          lead( SEQ ) over ( partition by id order by seq ) next_seq,
          lead( action ) over ( partition by id order by seq ) next_action
   FROM table1 T
)
WHERE action = 'SUSPEND'
  AND next_action <> 'CLEAR'

demo: http://sqlfiddle.com/#!4/5ea45/8

Answer 2

OK. Now I steal fiddle from kordirko and concept from simon and cook it in my way.

CREATE TABLE TABLE1
(
   "ID"       INT,
   "SEQ"      INT,
   "ACTION"   VARCHAR2 ( 7 )
);

INSERT ALL
INTO   TABLE1 ( "ID", "SEQ", "ACTION" )
VALUES ( 12345.00, 2, 'SUSPEND' )
INTO   TABLE1 ( "ID", "SEQ", "ACTION" )
VALUES ( 12345.00, 3, 'RESUME' )
INTO   TABLE1 ( "ID", "SEQ", "ACTION" )
VALUES ( 12345.00, 4, 'CLEAR' )
INTO   TABLE1 ( "ID", "SEQ", "ACTION" )
VALUES ( 12345.00, 5, 'RESUME' )
INTO   TABLE1 ( "ID", "SEQ", "ACTION" )
VALUES ( 12345.00, 6, 'SUSPEND' )
   SELECT * FROM DUAL;

So what I understand from your problem is that its is not explained correctly initially. But the second expected result does the trick. (I might be still wrong)

WITH AFTER_CHECK
     AS (SELECT T.ID,
                T.SEQ,
                T.ACTION,
                CASE
                   WHEN ACTION = 'CLEAR'
                   THEN
                      1
                   WHEN LEAD ( ACTION ) OVER (PARTITION BY ID ORDER BY SEQ) =
                           'CLEAR'
                   THEN
                      1
                   ELSE
                      0
                END
                   AS IGNORE_CURRENT
         FROM   TABLE1 T)
SELECT *
FROM   (SELECT T.ID,
               T.SEQ,
               T.ACTION,
               LEAD ( SEQ ) OVER (PARTITION BY ID ORDER BY SEQ) NEXT_SEQ,
               LEAD ( ACTION ) OVER (PARTITION BY ID ORDER BY SEQ)
                  NEXT_ACTION
        FROM   AFTER_CHECK T
        WHERE  IGNORE_CURRENT = 0)
WHERE  ACTION = 'SUSPEND';

OUTPUT:

12345   2   SUSPEND 5   RESUME
12345   6   SUSPEND     

What I did was assigning a flag to check consecutive actions set to be ignored if they have an ACTION called CLEAR or a NEXT ACTION called CLEAR. Then I used kordirko's snippet of LEAD function to do the job with a filter on needed ACTION.

See the fiddle here