Simple Data Conditioning in the SELECT Statement - Parallel Transporter

Teradata Parallel Transporter User Guide

Product
Parallel Transporter
Release Number
15.00
Language
English (United States)
Last Update
2018-09-27
dita:id
B035-2445
lifecycle
previous
Product Category
Teradata Tools and Utilities

Simple Data Conditioning in the SELECT Statement

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.

Table 15 contains some examples of data conditioning using SELECT and the syntax for each.

 

Table 15: Data Conditioning Syntax Using SELECT 

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.