On Thu, 8 May 2008 13:56:21 -0700 (PDT), Robert Jones <rjones0@[EMAIL PROTECTED]
>
wrote:
>On 8 May, 19:01, jeff <jmoore...@[EMAIL PROTECTED]
> wrote:
>> On May 8, 1:54 pm, Robert Jones <rjon...@[EMAIL PROTECTED]
> wrote:
>>
>>
>>
>> > On 8 May, 15:40, jeff <jmoore...@[EMAIL PROTECTED]
> wrote:
>>
>> > > On May 8, 10:24 am, "Michael Mattias" <mmatt...@[EMAIL PROTECTED]
>
wrote:
>>
>> > > > "jeff" <jmoore...@[EMAIL PROTECTED]
> wrote in message
>>
>> > >
>news:c06426f2-e7e7-428c-9350-b3343d6a612c@[EMAIL PROTECTED]
>>
>> > > > >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?
>>
>> > > > ALL A.vehicles?
>>
>> > > > Then you can't us the conditionals that way, since that will omit
any
>> > > > a.vehicles which don't have any rows in b matching the
conditions.
>>
>> > > > You can change your select to return a 'nvl' (oracle) or
'nullif'
>> > > > (something) or CASE (I think that is standard) to return the rows
where the
>> > > > "b" results set columns will be null and eliminate the WHEREs ,
but there
>> > > are others here who write SQL far far better than do I.
>> > > > MCM- Hide quoted text -
>>
>> > > > - Show quoted text -
>>
>> > > Yeah that was kind of an overview, I did use NVL for the B.Table
>> > > fields to account for nulls. Thanks for your help
>>
>> > I don't know whether you can explicity state Left outer join in
>> > ProCOBOL, but if you can then presumably the syntax diagrams will
>> > explain how to use it. I think you use ON rather than WHERE for the
>> > matching criteria.
>>
>> > Alternately, in addition to Michael's suggestion, you could try a
>> > UNION for the select with the matching criteria together with a
select
>> > just for table A.
>>
>> > Good luck (I am rather rusty!)
>>
>> > Robert- Hide quoted text -
>>
>> > - Show quoted text -
>>
>> The way you are suggesting do I do a select within a select?
>
>No you do two selects separated/joined by a UNION ALL phrase, each
>select selects the same columns or their logical equivalents, which
>can be values as is the case here.
>
>e.g. something like (without referring to a manual)
>
>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'
> union all
>select a.vehicle
> ,''
> ,''
> ,''
> ,''
> from table1 a
> order by a.vehicle
>
>(UNION ALL should eliminate any identical rows, which UNION alone does
>not)
You got it backwards -- Union eliminates dups, Union All does not.
b.vehicle(+) in the select list is invalid.
Unmatched quotes in the bottom select are invalid. They should say NULL.
It doesn't matter because there are no duplicates to eliminate. Written
your way, the a
vehicles that match b vehicles will appear twice -- once with b data to
the right and once
without.
>I don't understand the syntax/meaning of b.vehicle(+), but I expect
>you do.
It's Oracle syntax for LEFT OUTER JOIN.
|
