Re: SQL LEFT OUTER join

[Adam Glauser <adamglauser@xxxxxxxxxxxx>]

Trevor Perry wrote:
> All,
>
> I am using a LEFT OUTER JOIN. [...]
> The field I need from the ~right~ file is being filled with NULL when there
> is no record there. I really would like blanks in that field rather than
> NULL. 

You can use CASE with IS NULL.  For example:

with t1 as (select 1 as k1 from sysibm.sysdummy1
               UNION
             select 2 as k1 from sysibm.sysdummy1),
     t2 as (select 1 as fk1,'A' as ref from sysibm.sysdummy1
               UNION
            select 1 as fk1,'B' as ref from sysibm.sysdummy1)

select k1, case when ref IS NULL then '' else ref end
from t1 left outer join t2
     on k1 = fk1