NULLIF Equivalent in TPT for Fixed-Length File

Tools
Fan

NULLIF Equivalent in TPT for Fixed-Length File

I have a need to load fixed-length file into Teradata using TPT. So I defined the FORMAT='TEXT' in DataConnector  Producer. However, I have columns that have BLANKs. which need to be converted to NULL value in TD table. I was able to use NULLIF='' in Fastload easily. Fastload will convert the blanks to NULL value. 

 

However, in TPT Load operator, I was not able to use NULLIF successfully. The TPT User Reference manual mentioned CASE WHEN/ELSE solution. So I put the following logic in the LOAD Operator. The CUST_ID is defined as CHAR(10) in input file schema. Unfortunately, the CASE statement always return FALSE result. However, if I put CUST_ID='          '  (10 spaces), it will return TRUE. It looks like TPT does not ignore the trailing spaces when doing the string comparison. I have other columns that are about 100 to 200 characters long. I really hate to put so many spaces in the CASE statement. Does anyone have any idea how to get an easy NULLIF equivalent in TPT when the input file is fixed-length? Thanks.

 

STEP stLOAD_FILE_NAME
(
APPLY
('INSERT INTO ' || @TargetTable || '(
CUST_ID,
)
VALUES (
:CUST_ID,
);')
TO OPERATOR (ol_TARGET_TABLE)
SELECT
CASE WHEN CUST_ID='' THEN NULL ELSE CUST_ID END AS CUST_ID,
FROM OPERATOR(op_INPUT_FILE);
);

3 REPLIES
Teradata Employee

Re: NULLIF Equivalent in TPT for Fixed-Length File

The CASE logic does exactly what you tell it to do. It will compare the contents of the field with what you place in the CASE statement.

For fixed-length data, there is no concept of an empty string.

And so if you want to NULL a column based on a specific value, that entire fixed length value needs to be specified in the CASE statement.

The NULLIF clause in the FastLoad script should have worked the same way.

If it did not, I would like to see the syntax you used in the FastLoad script to NULL a column whose field data was a fixed length field of all space characters.

 

Thanks!

 

-- SteveF
Fan

Re: NULLIF Equivalent in TPT for Fixed-Length File

Here is what I used in Fastload script when reading a fixed-width file:

 

DEFINE
CUST_ID(CHAR(12), NULLIF=''),
CMPN_TYPE_CD(CHAR(3), NULLIF=''),
UIK_1_KEY(CHAR(20), NULLIF=''),
UIK_2_KEY(CHAR(20), NULLIF=''),
FILE=${strFileName} ;

The NULLIF() in Fastload works perfectly to convert all-space columns to NULL in target table.

 

And I am OK to use the CASE statement in TPT. But it does not allow me to use TRIM() or CAST() functions. This makes the code so tedious to check blanks.

 

 

Teradata Employee

Re: NULLIF Equivalent in TPT for Fixed-Length File

Hmmm, I did not think FastLoad would do that. Someone must have changed the code after I worked on it (back in the 90's).   Smiley Very Happy

We will look into this as a future enhancement for TPT (yeah, I know it does not help you today).

 

-- SteveF