Andreas Mosmann wrote:
> AK schrieb am 06.06.2005 in
> <1118087102.503676.238640_at_g47g2000cwa.googlegroups.com>:
>
>> 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
>> 6 rows selected
>
>
>> this way your code is easier to read and more maintainable
>
> you are right, I stopped thinking after understanding the query (it was
> 11pm), thank you, I will change this
>
> Andreas
>
But you could also drop your table and do audit session instead, but that would
not be related to your application, Oracle would deal with that.
Just an idea.
Gerard
Received on Thu Jun 09 2005 - 12:46:34 CDT
