RE: embedded SQL....

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

Jake, you can simulate a full outer join with the following:

Select t1.*, t2.* 
>From T1 left outer join T2 on(t1.k=t2.k)
union
Select t1.*, t2.*
>From T2 exception join T1 on(t1.k=t2.k)


Notes: 
1. select lists in both queries must be explicitly defined, so that fields
match in the final query.
2. result will contain nulls in both T1 and T2.  Use coalesce() on your
select fields to assign default values if necessary (blanks or 0).

I agree, I'd like to see support for full outer join, but it's not a show
stopper...

hth,

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-297-2863 or ext. 1863



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Jake M
Sent: Monday, April 17, 2006 12:05 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: embedded SQL....


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.
>
>