Teradata PT scripts can accomplish a variety of simple data conditioning tasks. Conditioning tasks that do not require a filter operator are specified in the executable section of the job script.
The following table contains some examples of data conditioning using SELECT and the syntax for each.
|Filtering Task||Code Example|
|Rename a column from “price” to “original price”||
SELECT price AS original_price
|Assign new values to a column based on the value of other columns, literals, and arbitrary expressions, such as calculating a discounted price of 20% off the base price.||
SELECT price*0.8 AS discounted_price
|Assign NULL values to a column.||
SELECT NULL(VARCHAR(n)) AS product_name
where n is the size of the VARCHAR column as defined in the DEFINE SCHEMA statement.
|Concatenate columns using the concatenation operator (||), such as loading both an area code and the telephone number into the same column to create a customer number||
SELECT AREA_CODE||PHONE_NUMBER AS CUSTOMER_NUMBER
|Load a value into a column that does not exist in the source, such as putting the value “123” in a column called “JOB_ID”||
SELECT '123' as JOB_ID
|Assign different values to a column in an output row based on conditions on columns in the corresponding input row, using a CASE value expression.||
APPLY 'INSERT INTO SALES_TABLE (original_price, discounted_price, product_name)' TO OPERATOR (UPDATE_OPERATOR ) SELECT price AS original_price, price*0.8 AS discounted_price, CASE WHEN product_name = ' ' THEN NULL(VARCHAR) ELSE product_name END AS product_name FROM OPERATOR (DATA_CONNECTOR )
For command syntax details, see “APPLY Statement” in Teradata Parallel Transporter Reference (B035-2436).