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)
I don't understand the syntax/meaning of b.vehicle(+), but I expect
you do.
Good luck
Robert
|
