Syntax | Retain Function | Vantage Analytics Library - Syntax - Vantage Analytics Library

To perform only retain transformations, use the following syntax. To perform more than one kind of transformation on the same input table, see Syntax.

CALL td_analyze (
  'vartran',
  'database = input_database_name;
   tablename = input_table_name;
   [ optional_parameter; [...] ]
   retain_transformation'
);

optional_parameter

{ fallback = { true | false } |
  gensqlonly = { true | false } |
  index = column_name [,...] |
  indexunique = { true | false } |
  lockingclause = SQL_LOCKING_clause |
  multiset = { true | false } |
  noindex = { true | false } |
  nullstyle = null_style |
  outputdatabase = output_database_name |
  outputstyle = { select | table | view | volatile } |
  outputtablename = output_table_name |
  overwrite = { true | false } |
  whereclause = SQL_WHERE_clause
}

retain_transformation

retain = { transformation | { transformation } ... };

You must type the colored or bold braces.

transformation

columns (column_spec [,...])

column_spec

column_name[/transformed_column_name]... [, datatype (data_type) ]

Syntax Elements

database

The database containing the input table.

tablename

The input table from which to build a predictive model.

fallback

[Optional] Whether to create a mirrored copy of the output table in Analytics Database when outputstyle=table.

Default: false

gensqlonly

[Optional] True returns the SQL for the function as a result set but does not run it.

False runs the SQL for the function but does not return it as a result set.

Default: false

index

[Optional] The index column or columns to include in the output table if outputstyle=table or outputstyle=volatile.

indexunique

[Optional] Whether to include a unique primary index in the output table when outputstyle=table or outputstyle=volatile.

Default: false

lockingclause

[Optional] The LOCKING clause to include in the generated SQL. Example:

LOCKING mydb.mytable FOR ACCESS;

multiset

[Optional] Whether the output table can contain duplicate rows when outputstyle=table or outputstyle=volatile.

Default: false

noindex

[Optional] Whether the output table contains no index columns (NOPI) when outputstyle=table or outputstyle=volatile.

Requires multiset=true.

Default: false

nullstyle

[Optional] A null_style and its parameters, if any, from the following table.

null_style and Parameters	Data Type
literal, value	NUMERIC, CHARACTER, or DATE If value is a DATE, precede value with keyword DATE and do not enclose value in single quotation marks.
mean	NUMERIC or DATE
median	NUMERIC or DATE
medianwithoutaveraging	Any supported data type
mode	Any supported data type
imputed, tablename	Any supported data type tablename must exist in the input database and contain a matched column for each column having null replacement.

Requires keycolumns=true.

outputdatabase

[Optional] The database containing the resulting output table when outputstyle=table or outputstyle=view.

Disallowed with outputstyle=volatile.

When the volatile outputstyle is specified, the user ID is automatically used as the ouputdatabase.

outputstyle

[Optional]

Option	Description
select (default)	Function outputs SELECT statement.
table	Function outputs table.
view	Function outputs view.
volatile	Function outputs volatile table.

outputtablename

[Optional] The name of the output table when outputstyle=table, outputstyle=view, or outputstyle=volatile.

When no outputstyle is defined, no output table is created.

overwrite

[Optional] Whether to drop the output tables before creating new ones.

Default: true

whereclause

[Optional] The expression in the SQL WHERE clause to include in the generated SQL to filter rows selected for analysis.

Examples:

• whereclause = cust_id > 0 includes this WHERE clause in the generated SQL:

  WHERE cust_id > 0

• whereclause = gender = ''F'' includes this WHERE clause in the generated SQL:

  WHERE gender='F'

columns

The input table column or columns to transform.

The function transforms each column in transformation separately.

If you specify transformed_column_name, it is the name of the transformed column in the output table.

If you omit transformed_column_name, the name of the transformed column in the output table is column_name.

datatype

[Optional] The output data type, a datatype from the following table, which the function casts to the equivalent SQL format if it is compatible with the transformed data.

datatype	SQL Format
DATATYPE(BIGINT)	BIGINT
DATATYPE(BYTEINT)	BYTEINT
DATATYPE(CHAR,n)	CHAR(n)
DATATYPE(DATE)	DATE
DATATYPE(DECIMAL,m,n)	DECIMAL(m,n)
DATATYPE(FLOAT)	FLOAT
DATATYPE(INTEGER)	INTEGER
DATATYPE(NUMBER)	NUMBER(*)
DATATYPE(NUMBER,n)	NUMBER(n)
DATATYPE(NUMBER,*,n)	NUMBER(*,n)
DATATYPE(NUMBER,n,n)	NUMBER(n,n)
DATATYPE(SMALLINT)	SMALLINT
DATATYPE(TIME,p)	TIME(p)
DATATYPE(TIMESTAMP,p)	TIMESTAMP(p)
DATATYPE(VARCHAR,n)	VARCHAR(n)