0

I am using TTU (fastload) to load data into DB table. Following is the fastld script for data load:

LOGON 127.0.0.1/test,*****;  
DATABASE MyDb; 
BEGIN LOADING "MyTable" 
ERRORFILES error_table_1_607712459, error_table_2_1744826307 
CHECKPOINT 0; 
SET RECORD VARTEXT DELIMITER ',' QUOTE YES '"' ; 
DEFINE in_field_0 (VARCHAR(1000)),in_field_1 (VARCHAR(1000)), 
FILE = /tmp/fastload_data_file390318d5-b42d-47d6-8c0f-6822a0bbb6ef.dat;
INSERT INTO "MyTable" ("data","num" ) VALUES (:in_field_0,:in_field_1); 
END LOADING; 
LOGOFF;

Data file: fastload_data_file390318d5-b42d-47d6-8c0f-6822a0bbb6ef.dat

"Hello World", "1"
"","2"
" ", "3"
null, "4"

After load: MyTable

    msg          num
    Hello World  1
    null         2
                 3
    null         4

When the data file has empty string (""), fastload inserting null into the DB. I have tried using TTU 16.20 and 17.00 on Ubuntu OS, behaviour is same across the versions. I am wondering, how to avoid inserting Null into the table. Any suggestions?

user1211004
  • 29
  • 3
  • I think you need to include `nullif` in your column definition - `field_0 (VARCHAR(1000),nullif='')` – Andrew Aug 12 '21 at 16:41
  • @Andrew I dont want to insert null values, expecting fastload to insert empty strings into the table. In the above example for 2nd row, fastld inserting null into the table for empty string. I want to avoid that. – user1211004 Aug 12 '21 at 16:45
  • Crap, I've gotten it backwards, nullif is the opposite of what you want. In your insert try to use coalesce - `...values ( COALESCE(:msg,'' '')`. Not sure if that's valid for fastload. – Andrew Aug 12 '21 at 17:05
  • 1
    COALESCE is not valid for FastLoad or TPT LOAD. You would need to use some other utility such as MultiLoad or TPT UPDATE. – Fred Aug 12 '21 at 17:59
  • Yes @Fred, you are correct. I see that, its a limitation from fastload utility. They mentioned it in Null field section of their doc page https://docs.teradata.com/r/r_6Z4JwVMhANtZFCIKEx7Q/vkmVYKKoDp0Hqvud6nHeiw – user1211004 Aug 14 '21 at 01:28

0 Answers0