Archives of the TeradataForum

Message Posted: Mon, 06 Oct 2008 @ 17:07:13 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hello;

Per chapter 9 of the Teradata "SQL Rererence: Functions and Operartors" [B035-1145-067A] for Release 12.0, if any of the values being concatenated are NULL, then the result will be NULL.

Not sure why you are seeing "?" and not "." per your formatting statement. When I reran the code you sent, my output file contained ".". Perhaps it is an earlier version of Teradata behaved differently? Or were you using Teradata SQL Assistant (which does not respect formatting) to select from the file?

Anyway, assuming there would be no reason to code something like you example, if we assume your intention is to concatenate a column that contains a null value, then your best bet will be to simply use a CASE statement so if the column being included is null, a literal "." will replace it, as shown here:

     SELECT
     'A'||';'||'A'||';'||'A'||';'||(CASE WHEN ABC.fake_col is NULL then '.'
     ELSE ABC.fake_col END)
     ||';'||'A'||';'||'A'||';'||'A'||';'||'A'||';'||'A'||';'||'A'||';'||'A'(T
     ITLE '')
     FROM
     (select NULL) ABC (fake_col);

This works in both Teradata SQL Assistant and in BTEQ, including writing the answer set out to a file.

Terry M. McLeod
TERADATA Professional Services, Retail - West

	<Prev	Next>		<<First	<Prev	Next>	Last>>