RE: Left join question (was: SQL table joins: Join vs. Where)

Vern,

You know, I never caught that.  Saw the "IFNULL function is identical to the
COALESCE scalar function..." but the whole statement "IFNULL function is
identical to the COALESCE scalar function with two arguments."  didn't
register.

Thanks for setting me straight.

Charles


> -----Original Message-----
> From: Vern Hamberg [mailto:vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx]
> Sent: Thursday, August 12, 2004 3:26 PM
> To: Midrange Systems Technical Discussion
> Subject: RE: Left join question (was: SQL table joins: Join vs. Where)
> 
> 
> With this difference - IFNULL is limited to 2 parameters, 
> COALESCE (or its 
> equivalent, VALUE) can have as many as you want.
> 
> Vern
> 
> At 11:50 AM 8/12/2004, you wrote:
> >ifnull is just another name for coalesce.
> >
> >You do have the SQL reference handy don't you?
> >
> >http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/db2/rb
> afzmst.pdf
> >
> >Charles
> >
> >
> > > -----Original Message-----
> > > From: Dan Bale [mailto:dbale@xxxxxxxxxxxxx]
> > > Sent: Thursday, August 12, 2004 11:56 AM
> > > To: Midrange Systems Technical Discussion
> > > Subject: RE: Left join question (was: SQL table joins: 
> Join vs. Where)
> > >
> > >
> > > > -----Original Message-----
> > > > From: midrange-l-bounces@xxxxxxxxxxxx / Goodbar, Loyd
> > > > Sent: Thursday, August 12, 2004 9:19 AM
> > > >
> > > > Where you have T04.CUSNM in your SELECT ALL, you could 
> substitute:
> > > >
> > > > Coalesce(T04.CUSNM, '* Customer ' concat trim(T01.CUNOCV)
> > > concat ' not
> > > > found. *')
> > > >
> > > > This will show you the missing customers when you change to your
> > > > left join.
> > >
> > > Coalesce is a beautiful thing!  Thanks Loyd!  (And Wayne;
> > > I'll have to learn
> > > more about ifnull)
> > >
> > > db
> > >
> > > --
> > > This is the Midrange Systems Technical Discussion
> > > (MIDRANGE-L) mailing list
> > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives
> > > at http://archive.midrange.com/midrange-l.
> > >
> >--
> >This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> >To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> >To subscribe, unsubscribe, or change list options,
> >visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> >or email: MIDRANGE-L-request@xxxxxxxxxxxx
> >Before posting, please take a moment to review the archives
> >at http://archive.midrange.com/midrange-l.
> 
> 
> --
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>