Re: embedded SQL....

["Jake M" <jakeroc@xxxxxxxxx>]

Gang...
I really wish DB2 sql engine would support the 'full outer join'. The
following SQL code works perfectly on SQL server but not on DB2...but the
previous solution that I posted works great on DB2 with a few performance
issues I am trying to fine tune...

*********************************************************************************************
SELECT
--CASE statement gets the CSNUMB from Table1 if it is there
--and gets it from Table2 otherwise
CASE
   WHEN Table1.CSNUMB1 IS NOT NULL
   THEN Table1.CSNUMB1
   ELSE Table2.CSNUMB2
END AS CSNUMB,

--Same as before but with the CAT field
CASE
   WHEN Table1.CAT1 IS NOT NULL
   THEN Table1.CAT1
   ELSE Table2.CAT2
END AS CAT,
   ISNULL(AMT2,0) AS AMT2,
   ISNULL(AMT1,0) AS AMT1

FROM

Table1

FULL OUTER JOIN

Table2 ON
   Table1.CSNUMB1 = Table2.CSNUMB2 AND
   Table1.CAT1 = Table2.CAT2

ORDER BY CSNUMB, CAT

****************************************************************

HTH,

Jake.

On 4/17/06, Wes <destaw@xxxxxxxxxxxxxxxxxxx> wrote:
>
> That is correct. You would be able to get the same result as left outer
> join, union and exception join suggested in this thread..
>
>
>
> "rick baird" <rick.baird@xxxxxxxxx> wrote in
> message news:e3018b9e0604170943m19fd65bew75984b5e043d517@xxxxxxxxxxxxxxxxx
> > after thinking about it, would that be a join that includes records in
> > both files, regardless of matches in the other?
> >
> > for instance:
> >
> > -  - | 1 2
> > -  - | 1 3
> > 2 1 | 2 1
> > 2 2 | - -
> > 3 1 | 3 1
> > -  -  | 4 1
> >
> > On 4/17/06, rick baird
> <rick.baird@xxxxxxxxx> wrote:
> > > what's a full inner join?
> > >
> > > I'm trying to think of what other sort of join you'd need other than
> > > inner, left outer and exception.
> > >
> > > On 4/17/06, Wes
> <destaw@xxxxxxxxxxxxxxxxxxx> wrote:
> > > > I wonder when IBM will support FULL INNER JOIN...that would make
> life
> so
> > > > much easier
> > >
> >
> > --
> > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
> list
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l.
> >
> >
>
>
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>