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.

NULL handling in NOT IN clause - Scenario to handle NULL in Teradata

Very often, we try to find out the values from a table (employee) where the employee_ID is not in the table Resigned. 

One way of achieving that is to use a NOT IN clause. But, beware, since presence of NULL may give you the wrong information.

NULL in not exists results in no rows returned, because no NULL in Teradata is equal.
Moreover, any operation on NULL returns a NULL.

So, make sure to modify your inserts using the Coalesce(EMP_Salary, 0) for all retired employees.

The below example illustrates this unusual behavior of NULL:

Sample script:

/* Unusual behavior of NULL in NOT IN */

create table lci_work_tbls.tera11

(

col1 integer

) ;

insert into lci_work_tbls.tera11 values (1);

insert into lci_work_tbls.tera11 values (2);

insert into lci_work_tbls.tera11 values (3);

create table lci_work_tbls.tera12

(

col2 integer

) ;

insert into lci_work_tbls.tera12 values (1);

insert into lci_work_tbls.tera12 values (null);

insert into lci_work_tbls.tera11 values (3);

/* Test to find the rows returned using NOT IN */

select col1 from lci_work_tbls.tera11

where col1 not in (select col2 from lci_work_tbls.tera12)

;

Result - NO ROWS RETURNED

Like us on Facebook or Google+ if you like our posts. Share what topics you want us to cover.

Please donate if you are helped. Your donation will help those who need help !!