Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with joins
"Raj" <raj.kothary_at_thus.net> wrote:
>
>"Turkbear" <john.greco_at_dot.state.mn.us> wrote in message
>news:3n2slv8mvr8b4bkrkr0ueum654olplc7bc_at_4ax.com...
>> "Raj" <raj.kothary_at_thus.net> wrote:
>>
>[snip]
>> Time ( no pun intended) for an Outer Join!
>
>:O)
>
>> 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') (+)
>
>I tried what you have suggested, however I get the following error:
>
>SQL> /
>and c2.CLIENT_FUNCTION_TYPE like 'LNS' (+)
> *
>ERROR at line 4:
>ORA-00933: SQL command not properly ended
>
>Any ideas?
>
>Thanks,
>Raj
>
Using LIKE requires a wildcard:
% is used to mean 'any number of characters'
_ is used to mean 'one character'
so for ALL your LIKE expressions add a wildcard:
c2.CLIENT_FUNCTION_TYPE like 'LNS%' (+)
hth, Received on Tue Sep 09 2003 - 12:57:04 CDT