SQL QUERY Oracle log analysis

Question

I have a log in table that have something like this:

tablelog

date      |  time   | event      | program   | ordendate
20130722    070000    executing    program1    20130722
20130722    070040    end ok       program1    20130722
20130722    070100    executing    program1    20130722
20130722    070140    end ok       program1    20130722

I have a query

 select a.date || a.time as datetimeStart, 
        b.date || b.time as datetimeStop, 
        a.program, a.ordendate  
   from tablelog a, 
        tablelog b
  where a.date || a.time < b.date || b.time
    and a.event = "executing"
    and b.event = "end ok"

This return 3 executions but only I have 2... How can I fix this query???

Thank you!!

Why it should return 3 record? I see just 2 records that satisfy your query. — araknoid
– araknoid, Commented Jul 22, 2013 at 15:51
What's preventing the last event from matching the very first event in your query? — Mat
– Mat, Commented Jul 22, 2013 at 15:57
You should never store dates and timestamps in varchar columns. Why don't you using a single timestamp instead? — user330315
– user330315, Commented Jul 22, 2013 at 15:57

Joachim Isaksson · Accepted Answer · 2013-07-22 16:56:50Z

1

As far as I understand it, you want to list sequential start/stops by program(?) This uses LEAD to do the work in a CTE, then just filters and orders using an outer query;

WITH cte AS (
  SELECT 
    CASE WHEN "event"='executing' THEN "date" || "time" END "datetimeStart",
    LEAD(CASE WHEN "event"='end ok' THEN "date" || "time" END) 
    OVER(PARTITION BY "program" ORDER BY "date","time") "datetimeStop",
    "program", "ordendate"
  FROM tablelog
)
SELECT * FROM cte
WHERE "datetimeStart" IS NOT NULL 
  AND "datetimeStop"  IS NOT NULL
ORDER BY "datetimeStart"

An SQLfiddle to test with.

edited Jul 22, 2013 at 16:56

answered Jul 22, 2013 at 16:18

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joachim Isaksson · Accepted Answer · 2013-07-22 16:55:33Z

1

The query that you are trying to do is best done using the analytic functions lag() or lead():

select dateTimeStart, dateTimeStop, program, orderdate
from (select tl.date || tl.time as datetimeStart,
             lead(tl.date || tl.time) over (partition by program order by date, time) as dateTimeStop,
             tl.*
      from tablelog tl
     ) tl
where tl.event = 'Executing';

edited Jul 22, 2013 at 16:55

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

answered Jul 22, 2013 at 16:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Max · Accepted Answer · 2013-07-22 16:09:05Z

0

I think, the above will return the following 3 records :-

first record matched with second record
first record matched with fourth record
third record matched with fourth record.

To resolve that, try the following query (untested) :-

select * from
(select a.date || a.time as datetimeStart, 
        b.date || b.time as datetimeStop, 
        a.program, a.ordendate, row_number() over (partition by (a.date || a.time) order by (a.date || a.time)) rn  
   from tablelog a, 
        tablelog b
  where a.date || a.time < b.date || b.time
    and a.event = "executing"
    and b.event = "end ok")
where rn =1

answered Jul 22, 2013 at 16:09

Max

4,0871 gold badge21 silver badges29 bronze badges

Collectives™ on Stack Overflow

SQL QUERY Oracle log analysis

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related