Derive

In-Database Analytic Functions User Guide

brand
Software
prodname
Teradata Warehouse Miner
vrm_release
5.4.2
category
User Guide
featnum
B035-2306-106K

Purpose

The Derive transformation requires that the free-form transformation be specified as a formula using the following operators, arguments and functions:

+, -, **, *, /, %, (, ), x, y, z, abs, exp, ln, log, sqrt

The arguments x, y, and z may only assume the value of an input column, unlike in the classic version of this transformation. It is recommended that the formula be enclosed in pairs of single quotes for correct processing (see example below). Notice also that an implied multiply operator is automatically inserted when a number, argument (x, y, z) or parenthesis is immediately followed by an argument or parenthesis. For example, 4x means 4*x, xy means x*y, and x(x+1) is equivalent to x*(x+1).

An example of the formula for the quadratic equation is given below.

formula (''(-y+sqrt(y**2-4xz))/(2x)'')

Syntax

call twm. td_analyze('vartran','database=database name;tablename=table name;General Parameters;derive={formula (arithmetic formula),arguments (values),outputname (output name)};');

Required Parameters

arguments
The arguments parameter is used to specify the columns to be associated with the arguments x, y and z. If the formula parameter includes only the argument x, then the arguments parameter should include only one column. If it includes the arguments x and y, then the arguments parameter should include two column names. And finally, if the formula includes the arguments x, y and z, the arguments parameter should include three column names.
In the unusual case where the formula defines a constant expression, the arguments parameter is not required. However, at least one column must be included in the overall transformation, perhaps in a Retain transformation.
database
The database containing the input table.
derive
The parameter that identifies the type of transformation being performed.
formula

The Derive transformation requires that the free-form transformation be specified as a formula using the following operators, arguments and functions:

+, -, **, *, /, %, (, ), x, y, z, abs, exp, ln, log, sqrt

The arguments x, y, and z may only assume the value of an input column, unlike in the classic version of this transformation. It is recommended that the formula be enclosed in pairs of single quotes for correct processing. Notice also that an implied multiply operator is automatically inserted when a number, argument (x, y, z) or parenthesis is immediately followed by an argument or parenthesis. For example, 4x means 4*x, xy means x*y, and x(x+1) is equivalent to x*(x+1).

An example of the formula for the quadratic equation is given below.

formula ("(-y+sqrt(y**2-4xz))/(2x)")

outputname
This parameter controls the naming of the transformed output column for the Derive transformation.
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.
keycolumns
When null replacement is requested, either via a Null Replacement transformation or in combination with a Bin Code, Derive, Design Code, Recode, Rescale, Sigmoid or Z Score transformation, the keycolumns parameter must be specified. The column or columns listed must form a unique key into the input and output table of the transformation.
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.
nullstyle
Data types supported by various nullstyle parameters are:
Data Type Description Example
literal,value numeric, character, and date nullstyle (literal,value)
mean numeric and date nullstyle (mean)
median numeric and date nullstyle (median)
medianwithoutaveraging any supported data type nullstyle (medianwithoutaveraging)
mode any supported date type nullstyle (mode)
imputed,table any supported data type nullstyle (imputed,tablename)

If date values are entered, the keyword DATE must precede the date value, which should not be enclosed in single quotes.

outputdatabase
The database that will contain the resulting output table when outputstyle=table or view.
outputstyle
The allowed output styles are:
  • select
  • table
  • view
If outputstyle is not specified, the function generates a SELECT statement and does not create a table or view.
outputstyle={select||table||view}
outputtablename
The name of the output table when outputstyle=table or 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 describe how to use Derive. These examples assume that the td_analyze function has been installed in a database named twm.

The following example includes two formulas: one with 3 variables (x, y and z), and the other with one (x). Notice that the second formula, x(x+1), is enclosed in pairs of single quotes, necessitated by the presence of parentheses in the formula. Doubling the single quotes is necessary because the parameters are already contained within a string literal. It may be advisable to always include the pairs of single quotes around the formula to avoid confusion.

call twm.td_analyze('vartran','database=twm_source;tablename=twm_customer;derive={formula(x+y+z),arguments(income,age,years_with_bank),outputname(myoutput)}{formula(''x(x+1)''),arguments(age),outputname(myoutput2)};');

The following example demonstrates combined null replacement. Note that keycolumns must be included as a general parameter when null value replacement is performed.

call twm.td_analyze('vartran','database=twm_source;tablename=twm_customer;keycolumns=cust_id;derive=formula(''x(x+1)''),arguments(age),outputname(myoutput),nullstyle(literal,0);');