Your query works good but may be there is something in here...
GM, May 16, 2005 - 2:28 pm UTC
Here is my example table:
CREATE TABLE TEST_EVENT
(
ID NUMBER,
EVENT_KEY VARCHAR2(10),
EVENT_NAME VARCHAR2(10),
SUB_EVENT VARCHAR2(10)
)
and here is what I am trying to do
CREATE OR REPLACE VIEW TEST_EVENT_VIEW AS
SELECT D.ID, D.EVENT_KEY, D.EVENT_NAME, D.SUB_EVENT,
(CASE WHEN D.EVENT_NAME <> D.EVENT_BEFORE THEN 1 ELSE NULL END) IS_FIRST_SUB_EVENT,
(CASE WHEN D.EVENT_NAME <> D.EVENT_AFTER THEN 1 ELSE NULL END) IS_LAST_SUB_EVENT,
MAX (DECODE ((CASE WHEN D.EVENT_NAME <> D.EVENT_BEFORE THEN 1 ELSE NULL END), NULL, NULL, D.ID)) OVER (PARTITION BY D.EVENT_KEY, D.EVENT_NAME ORDER BY D.ID) EVENT_RUN_ID
FROM (SELECT STG.*,
LAG (EVENT_NAME, 1, 'START') OVER (PARTITION BY EVENT_KEY ORDER BY ID, EVENT_NAME) EVENT_BEFORE,
LEAD (EVENT_NAME, 1, 'END') OVER (PARTITION BY EVENT_KEY ORDER BY ID, EVENT_NAME) EVENT_AFTER
FROM TEST_EVENT STG) D;
I am trying to generate event_run_id for related sub-events to be the id value of first sub-event's id value.
Here is my first data-set:
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 1, 'EK1', 'E1', 'S1');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 2, 'EK1', 'E1', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 3, 'EK1', 'E2', 'S1');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 4, 'EK1', 'E2', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 5, 'EK1', 'E1', 'S1');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 6, 'EK1', 'E1', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 7, 'EK2', 'E1', 'S1');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 8, 'EK2', 'E1', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 9, 'EK2', 'E2', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 10, 'EK2', 'E3', 'S3');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 11, 'EK3', 'E1', 'S1');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 12, 'EK3', 'E1', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 13, 'EK3', 'E2', 'S1');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 14, 'EK3', 'E2', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 15, 'EK4', 'E2', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 16, 'EK4', 'E2', 'S3');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 17, 'EK4', 'E3', 'S1');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 18, 'EK4', 'E3', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 19, 'EK4', 'E2', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 20, 'EK4', 'E2', 'S3');
COMMIT;
This works fine. Here is my second data set and it has problem while generating the event_run_id 7 (its picked up twice for some reason) when I select from the view
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 1, 'EK1', 'E1', 'S1');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 2, 'EK1', 'E2', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 3, 'EK1', 'E3', 'S1');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 4, 'EK1', 'E1', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 5, 'EK1', 'E2', 'S1');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 6, 'EK1', 'E3', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 7, 'EK2', 'E1', 'S1');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 8, 'EK2', 'E2', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 9, 'EK2', 'E3', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 10, 'EK2', 'E1', 'S3');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 11, 'EK3', 'E1', 'S1');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 12, 'EK3', 'E2', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 13, 'EK3', 'E3', 'S1');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 14, 'EK3', 'E1', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 15, 'EK4', 'E1', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 16, 'EK4', 'E2', 'S3');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 17, 'EK4', 'E3', 'S1');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 18, 'EK4', 'E1', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 19, 'EK4', 'E2', 'S2');
INSERT INTO TEST_EVENT (ID, EVENT_KEY, EVENT_NAME, SUB_EVENT) VALUES ( 20, 'EK4', 'E3', 'S3');
COMMIT;