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 [4]) 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 [2]) |
For command syntax details, see “APPLY Statement” in Teradata Parallel Transporter Reference (B035-2436).