Re: Help with joins

From: Raj <raj.kothary_at_thus.net>
Date: Tue, 9 Sep 2003 23:42:46 +0100
Message-ID: <bjlkss$nrs$1$8300dec7@news.demon.co.uk>

[snip]
> Try:
>
> select distinct c1.USER_NAME as USER_NAME, c1.CALLED_STATION_ID, c1.TIME
as
> START_T, c2.TIME as STOP_T
> from ACCOUNTING c1, ACCOUNTING c2
> where c1.CLIENT_FUNCTION_TYPE like 'LNS'
> and c2.CLIENT_FUNCTION_TYPE like 'LNS' (+)
> and c1.USER_NAME=c2.USER_NAME (+)
> and c1.ACCT_STATUS_TYPE like 'Start'
> and c2.ACCT_STATUS_TYPE in ('Stop', NULL) (+)
> and (TO_DATE(c1.TIME,'yyyy/mm/dd HH24:mi:ss') <=
TO_DATE(c2.TIME,'yyyy/mm/dd
> HH2
> 4:mi:ss') (+)

Just ot let you know, the outer joins on the above statement did not work. This was the solution I ended up having to use, for your reference:

select c1.CALLED_STATION_ID, c1.USER_NAME, c1.TIME as START_T, c2.TIME as STOP_T
from (select ACCT_SESSION_ID, CALLED_STATION_ID, TIME, USER_NAME

      from CDU_ACCOUNTING
      where CLIENT_FUNCTION_TYPE = 'LNS'
      and ACCT_STATUS_TYPE like 'Start'
      and TO_DATE(TIME,'yyyy/mm/dd HH24:mi:ss') <= to_date('2003/09/09
23:59:59','yyyy/mm/dd HH24:mi:ss')
      and TO_DATE(TIME,'yyyy/mm/dd HH24:mi:ss') >= to_date('2003/09/09
00:00:00','yyyy/mm/dd HH24:mi:ss')) c1,
     (select ACCT_SESSION_ID, TIME
      from CDU_ACCOUNTING
      where CLIENT_FUNCTION_TYPE = 'LNS'
      and ACCT_STATUS_TYPE like 'Stop'
      and TO_DATE(TIME,'yyyy/mm/dd HH24:mi:ss') >= to_date('2003/09/09

18:00:59','yyyy/mm/dd HH24:mi:ss')) c2
where c1.ACCT_SESSION_ID=c2.ACCT_SESSION_ID(+)

Thanks for taking the time to have a look though! Is there a more efficient way to do this?

Regards,
Raj Received on Tue Sep 09 2003 - 17:42:46 CDT