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

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