Data Conversions - Parallel Transporter

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 [1] )

SELECT * FROM OPERATOR
(EXPORT_OPERATOR [1]...

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 [1] )
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 [1]...

Notice that:

• 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.