Teradata PT’s ability to convert data is limited to assigning a data type to a null value or changing data from a null value. For example:
CAST (NULL AS INTEGER)
Using the CAST clause, you can convert to an alternate data type prior to loading data into a table. The following APPLY statement illustrates this option.
APPLY ('INSERT INTO CUSTOMER (:CUST_NUM, :LAST_NAME, :FIRST_NAME, :SOC_SEC_NO);') TO OPERATOR (LOAD_OPERATOR  ) SELECT * FROM OPERATOR (EXPORT_OPERATOR ...
Here, the use of SELECT * implies data is accepted as is from the Export operator; however, the data can also be converted.
If data is needed in a different table, create the following APPLY statement:
APPLY ('INSERT INTO CUSTOMER (:CUST_NUM, :LAST_NAME, :FIRST_NAME, :SOC_SEC_NO);') TO OPERATOR (LOAD_OPERATOR  ) SELECT CAST (NULL AS CHAR(10)) AS CUST_NUM, LAST_NAME, FIRST_NAME, CASE WHEN (SOC_SEC_NO <> '000000000') THEN SOC_SEC_NO ELSE NULL END AS SOC_SEC_NO, FROM OPERATOR (EXPORT_OPERATOR ...
- This example assumes that the schema of the source data is not the same as the schema of the target table.
- The first field is a derived column with a NULL value.
- The target Social Security number is assigned the NULL value if the source Social Security number is a string of all zero characters.
- This use of the CASE expression is comparable to the NULLIF function of the FastLoad utility.
- The functionality provided by the CASE expression is available to all Teradata PT operators because expressions are allowed in the APPLY statement.