Teradata Fastexport returning blank lines - Common problems of having NULL values in table - Example and Syntax

NULL handling is the one of the most important aspects of Reporting queries. 
So, if you face a situation where your Fastexport is returning blank lines, check the columns for NULL again.

For example, in the below script, CustEmailAddress is a NULL column. A customer may or maynot provide  his/her email address.
And for all rows where CustEmailAddress is NULL, the entire row becomes NULL on concatenation. Any operation on NULL results in a NULL.

Hence, these rows appear as blank rows in the export file.

So, we have to be careful while exporting such values in Fastexport.

Steps to be performed to avoid blank lines:

1. Use Coalesce function to send a default value if NULL exists
2. Also, if the columns are of different datatypes, the default value in COALESCE may vary.
3. For email columns(CustEmailAddress in the example below), a default of '0' may lead to misinterpretation of data. Chooose the default value carefully. We would suggest confirm the DEFAULT values from Downstream applications
4. Best practice is to store all columns as Varchar and then send "BLANK" instead of NULL

You can also use the below UNIX command to handle Blank rows in the fastexport output file:

sed '/^$/d' /tmp/outfile1.txt > /out/outfile_20130405.txt

Example Script:


/* Weird behavior of NULL */
/* If any column is null and coalesce is missing, appending the null column leads to an entire null row */

SELECT TRIM(DATA1)
     FROM
     (
     SELECT
     '¿'
     || trim(coalesce(SourceId,''))
     || '|' ||trim(coalesce(Dates,''))
     || '|' ||trim(coalesce(IPAddress,''))
     || '|' ||trim(coalesce(SalesNum,''))
     || '|' ||trim(coalesce(StoreNum,''))
     || '|' ||trim(coalesce(CustFirstName,''))
     || '|' ||trim(coalesce(CustEmailAddress,''))
     || '|' ||trim(coalesce(OrderValue,''))
     || '|' ||trim(coalesce(Sellerid,''))
     AS DATA1
     FROM
     db_work_tbls.CustOrder1
     )as A
     ;

Let us know if this information has helped. Like us on Facebook or Google+

Please donate as your donation will help someone in need.