"Robert" <no@[EMAIL PROTECTED]
> wrote in message
news:lj3724d5ohrvdm3vvl91gq43hk154hsa85@[EMAIL PROTECTED]
> 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?
>
> select * from
> ( (select a.vehicle, b.vehicle as 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'
> ) matches
> union
> select vehicle, null, null, null, null
> from table1 a
> where vehicle not in (select vehicle from matches)
> order by vehicle
> )
>
> There is a simpler way using DISTINCT, which applies to all columns
> FOLLOWING the word
> distinct.
>
> select vehicle, b_vehicle, acct, MOYR, trancode from
> (select b_vehicle, acct, MOYR, trancode, DISTINCT vehicle from
> (select a.vehicle, b.vehicle as b_vehicle, b.acct, b.MOYR, b.trancode,
> '1' as source
> from table1 a, table2 b
> where a.vehicle = b.vehicle
> and b.acct='1234'
> and b.MOYR = '0502'
> and b.trancode='80'
> union
> select vehicle, null, null, null, null, '2' as source
> from table1
> order by vehicle, source
> )
> )
Very impressive, Robert.
Thanks for posting this. I, for one, learned something.
Pete.
--
"I used to write COBOL...now I can do anything."
|
