Purpose
The retain option allows you to copy one or more columns as is into the final analytic data set. By default, the result column name is the same as the input column name, but this can be changed. If a specific type is specified, it results in casting the retained column.
The Retain transformation is supported for all valid data types.
Syntax
call twm. td_analyze('vartran','database=twm_source;tablename=twm_customer;General Parameters;retain={columns (cust_id,age,income)};');Required Parameters
- columns
- Controls the name of the output (transformed) column and its data type. The columns parameter is required by all transformations except Derive. A separate transformation is performed for each column in the list. If a column name is followed by a forward slash and a name, the name after the slash becomes the name of the transformed column in the resultant output table. Otherwise the column name is used as the output column name.
- database
- The database containing the input table.
- retain
- The parameter that identifies the type of transformation being performed.
- tablename
- The name of the table to be transformed.
- vartran
- This parameter is required to run a variable transformation. The vartran parameter is always enclosed in single quotes.
General Parameters (separated by semi-colons)
- datatype
- For all transformation types, the datatype parameter is used to cast the column to a desired database data type provided it is compatible with the transformed data. The allowed output types include:
- byteint
- char
- date
- decimal
- float
- integer
- smallint
- time
- timestamp
- varchar
- bigint
- number
- fallback
- When set to true, this parameter requests a mirrored copy of the output table in the Teradata Database when outputstyle=table.
- gensqlonly
- When set to true, the SQL for the requested transformations is returned as a result set but not executed. When this parameter is not specified or is set to false, the SQL is executed but not returned.
- indexcolumns
- When set to true, requests the output table contain the index columns when outputstyle=table.
- indexunique
- When set to true, requests the output table contain a unique primary index when outputstyle=table.
- lockingclause
-
Requests the generated SQL contain the given locking clause in the appropriate location depending on the output style.
An example of a locking clause when the output style defaults to select is:
LOCKING mydb.mytable FOR ACCESS;
- multiset
- When set to true, requests an output table that may contain duplicate rows when outputstyle=table.
- noindex
- When set to true, requests the output table contain no index columns when outputstyle=table.
- outputdatabase
- The database that will contain the resulting output table when outputstyle=table or view.
- outputtablename
- The name of the output table when outputstyle=table or view.
- outputstyle
- The allowed output styles are:
- select
- table
- view
- whereclause
- Requests the generated SQL contain the given WHERE clause in appropriate places in the generated SQL. This is independent of the output style requested.
Examples
Examples in this section show how to use Retain. These examples assume that the td_analyze function has been installed in a database named twm.
The first example demonstrates retaining multiple columns unchanged.
call twm.td_analyze('vartran','database=twm_source;tablename=twm_customer;retain={columns(cust_id,age,income)};');
The second example shows retaining some columns unchanged and some with name and data type change.
call twm.td_analyze('vartran','database=twm_source;tablename=twm_customer;retain={columns(cust_id,age)}{columns(income/inc),datatype(bigint)};');