Re: Help with joins

Raj wrote:

>[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
>
>
>
>

Undoubtedly. But how much are you going to pay Turkbear for doing you job for you? ;-)

Create statistics with DBMS_STATS, run explain plan, go to http://tahiti.oracle.com and learn how to analyze the output and make those tuning changes yourself.

BTW: Love the disclaimer at the end of your original posting: A Confidentiality notice to the entire planet.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)