Re: Oracle-Advanced SQL-question

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

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de