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