>>> On 5/8/2008 at 9:25 PM, in message
<mmg7245hbpg6m4ia9t8k3qd48l9mv3598t@[EMAIL PROTECTED]
>, Robert<no@[EMAIL PROTECTED]
> wrote:
> On Thu, 8 May 2008 06:58:32 -0700 (PDT), jeff <jmoore207@[EMAIL PROTECTED]
>
wrote:
>
>>I am having a problem with a left outer join when I add conditionals.
>>
>>Select
>> A. vehicle,
>> B.Vehicle,
>> B.acct,
>> B.MOYR,
>> B.Trancode
>> from Table1 A, Table2 B
>> where A.vehicle=B.vehicle(+)
>> and B.acct='1234'
>> and B.MOYR = '0502'
>> and B.trancode='80'
>> order by a.vehicle
>>
>>I need all a.vehicles for all vehicles to come back in the cursor
>>regardless. Any ideas?
>
> You might find ANS syntax more intuitive. It is available on Oracle 9
> and above.
>
> Select
> A. vehicle,
> B.Vehicle,
> B.acct,
> B.MOYR,
> B.Trancode
> from Table1 A LEFT OUTER JOIN Table2 B
> where A.vehicle=B.vehicle
> and B.acct='1234'
> and B.MOYR = '0502'
> and B.trancode='80'
> order by a.vehicle
>
> What if a vehicle appears in B but not in A? It cannot be done easily
> with the old syntax;
> it's a snap with ANS syntax. Just change LEFT OUTER JOIN to FULL OUTER
> JOIN.
I don't know about Oracle, but in DB2 the syntax would be
Select
A.vehicle,
B.Vehicle,
B.acct,
B.MOYR,
B.Trancode
from Table1 A LEFT OUTER JOIN Table2 B
on A.vehicle=B.vehicle
where B.acct='1234'
and B.MOYR = '0502'
and B.trancode='80'
order by a.vehicle
I think. Not an expert, and i didn't test it.
Frank
|
