Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle-Advanced SQL-question
One more thing: you don't have to repeat one and the same expression
twice, you can use an inline view aka table expression instead:
select CIDUSER, CTIMESTAMP, logout_time, round((logout_time -
CTIMESTAMP)*24*60)
from(
select CIDUSER, CTIMESTAMP,
(select min(CTIMESTAMP) from event e1 where e1.CSUBJECT='logout'
and e1.CIDUSER = event.CIDUSER and e1.CTIMESTAMP > event.CTIMESTAMP
and not exists(select * from event e2 where e1.CIDUSER = e2.CIDUSER
and event.CTIMESTAMP < e2.CTIMESTAMP and e2.CTIMESTAMP < e1.CTIMESTAMP
)
) logout_time
from event where CSUBJECT='login')iv
CIDUSER CTIMESTAMP LOGOUT_TIME SESSION_DURATION
------- --------------------- --------------------- ---------------- 1 12/10/2004 1 12/11/2004 1 12/12/2004 12/14/2004 2880 1 12/15/2004 1 12/16/2004 12/17/2004 1440 1 12/19/2004
this way your code is easier to read and more maintainable Received on Mon Jun 06 2005 - 14:45:02 CDT