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
|
