Concatenataion and Null

[mcauliff]

I have a select statement coded (extract below), 1 of the columns has a null value. When this happens the concatenation is all null. How do I check for null and bypass?

Select line1 || line2 || line3 || line4
from a.table
where date = '2007-06-20'

line4 is null.

 

[LKBrwnDBA]

Try:

Select Line1 || Line2 || Line3 || Line4
  From A.Table
 Where Date = '2007-06-20'
   And Line4 Is Not Null

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[mcauliff]

Thank forthe response. But as I researched Line2, line 3 and Line 4 may be Null.

I resolve using this

Line1 || IfNull(Line2,' ') || IfNull(Line3,' ') || IfNull(Line4,' ')

 

[Truusvlugindewind]

Personally I use the "VALUE" clause a lot (being a COBOL programmer it reliefs you from fiddling around with NULL-IMDICATORS all the time).

So I should code

value(line1 , ' ') || value(line2, 0 )  ect