Oracle Sequential actions

oracle, plsql

Solution

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

Problem

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.

Original source