On May 10, 12:57=A0pm, "Frank Swarbrick"
<Frank.Swarbr...@[EMAIL PROTECTED]
> wrote:
> >>> On 5/8/2008 at 9:25 PM, in message
> <mmg7245hbpg6m4ia9t8k3qd48l9mv35...@[EMAIL PROTECTED]
>, Robert<n...@[EMAIL PROTECTED]
> wrote:
> > On Thu, 8 May 2008 06:58:32 -0700 (PDT), jeff <jmoore...@[EMAIL PROTECTED]
>
> wrote:
>
> >>I am having a problem with a left outer join when I add conditionals.
>
> >>Select
> >> =A0 =A0 =A0 =A0A. vehicle,
> >> =A0 =A0 =A0 =A0B.Vehicle,
> >> =A0 =A0 =A0 =A0B.acct,
> >> =A0 =A0 =A0 =A0B.MOYR,
> >> =A0 =A0 =A0 =A0B.Trancode
> >> from =A0Table1 A, Table2 B
> >> where A.vehicle=3DB.vehicle(+)
> >> =A0and =A0 B.acct=3D'1234'
> >> =A0and =A0 B.MOYR =3D '0502'
> >> =A0and =A0B.trancode=3D'80'
> >> =A0order by a.vehicle
>
> >>I need all a.vehicles for all vehicles to come back in the cursor
> >>regardless. Any ideas?
>
> > You might find ANS syntax more intuitive. It is available on Oracle 9
> > and above.
>
> > Select
> > =A0 =A0 =A0 =A0 A. vehicle,
> > =A0 =A0 =A0 =A0 B.Vehicle,
> > =A0 =A0 =A0 =A0 B.acct,
> > =A0 =A0 =A0 =A0 B.MOYR,
> > =A0 =A0 =A0 =A0 B.Trancode
> > =A0from =A0Table1 A LEFT OUTER JOIN Table2 B
> > =A0where A.vehicle=3DB.vehicle
> > =A0 and =A0 B.acct=3D'1234'
> > =A0 and =A0 B.MOYR =3D '0502'
> > =A0 and =A0B.trancode=3D'80'
> > =A0 order by a.vehicle
>
> > What if a vehicle appears in B but not in A? It cannot be done easily
> > with the old syntax;
> > it's a snap with ANS syntax. Just change LEFT OUTER JOIN to FULL OUTER
> > JOIN.
>
> I don't know about Oracle, but in DB2 the syntax would be
>
> Select
> =A0 =A0 =A0 =A0 A.vehicle,
> =A0 =A0 =A0 =A0 B.Vehicle,
> =A0 =A0 =A0 =A0 B.acct,
> =A0 =A0 =A0 =A0 B.MOYR,
> =A0 =A0 =A0 =A0 B.Trancode
> =A0from =A0Table1 A LEFT OUTER JOIN Table2 B
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 on A.vehicle=3DB.vehicle
> =A0where B.acct=3D'1234'
> =A0 and =A0B.MOYR =3D '0502'
> =A0 and =A0B.trancode=3D'80'
> =A0order by a.vehicle
>
> I think. =A0Not an expert, and i didn't test it.
>
> Frank- Hide quoted text -
>
> - Show quoted text -
Thanks to all for all the great feedback.
|
