Re: Using SQl to obtain a field value from one file or another

Is
left join = left outer join
or
left join = left inner join.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Mike Jones <mike.jones.sysdev@xxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Date: 06/30/2017 03:55 PM
Subject: Re: Using SQl to obtain a field value from one file or
another
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Re:

select coalesce( nullif( FILE1.FIELD1, '' ), FILE2.FIELD1 ) as FIELD1
,coalesce( nullif( FILE1.FIELD2, '' ), FILE2.FIELD2 ) as FIELD2
from ...

NULLIF may not be needed in Alan's case, but it is needed in cases where a
row is retrieved as part of the FROM table or a LEFT JOIN table, and the
column value is non-null, but blank, and you want to ignore blanks (i.e.
you want to deliver the first non-blank value).

COALESCE used alone will deliver blank values, which may be desired in
some
cases, and not others.

NULLIFs inside COALESCEs is a common technique used on many databases, not
just ours.

Reminder: COALESCE handles cascading through many values, not just two as
in the above example.

Mike

message: 4
date: Fri, 30 Jun 2017 19:19:41 +0000
from: Tommy Holden <Tommy.Holden@xxxxxxxxx>
subject: RE: Using SQl to obtain a field value from one file or
another

The nullif() isn't required as coalesce will handle that for you.

Select file1.field1, coalesce(file2.field2,file3.field2) from...


Thanks,
Tommy Holden

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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD


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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD