Missing rows from file using Fast Export

Tools & Utilities

Missing rows from file using Fast Export

When I export a table using Fast Export, many rows of the original table are missing and replaced with empty rows in the resulting text file. The text file contains the same number of rows as the original table, however many of them are blank. Below is the fexp script I am using.

/* define restart log */;
.LOGTABLE utillog ;

/* DBC logon string */;
.LOGON username,password ;

/* specify export function & number of sessions to be used*/;
.BEGIN EXPORT sessions 5;

/* identify the destination file for exported data*/;
.EXPORT OUTFILE C:\Teradata_root\ACCT_TBL.txt
FORMAT TEXT
MODE RECORD
;

/* SQL SELECT statement */;

SELECT
CAST(TRIM(ACCT_CO_NBR)|| '|' ||
TRIM(ACCT_ID)|| '|' ||
TRIM(ACCT_AU)|| '|' ||
TRIM(CUST_NBR)|| '|' ||
TRIM(CUST_NAME_LINE_1)|| '|' ||
TRIM(CUST_SSN_TAXID_NBR)|| '|' ||
TRIM(BIRTH_DATE)|| '|' ||
TRIM(CITY)|| '|' ||
TRIM(STATE)|| '|' ||
TRIM(COUNTRY)|| '|' ||
TRIM(PRIM_CUST_IND)|| '|' ||
TRIM(BAL_AMT)|| '|' ||
TRIM(ACCT_OPEN_DATE)|| '|' ||
TRIM(ACCT_STATUS_CD)as CHAR(160))

FROM ADWP_WORK1.ACCT_TBL
;

/* terminate the export operation */;
.END EXPORT ;

/* disconnect from the DBS */;
.LOGOFF ;

1 REPLY
Teradata Employee

Re: Missing rows from file using Fast Export

Most likely one or more columns in the "missing" rows is NULL. Anything concatenated with NULL is also NULL; if you want NULLs translated to "empty string" you have to say so, e.g.
... COALESCE(TRIM(col),'')||'|'|| ...