Syntax | Recode Function | Vantage Analytics Library - Syntax - Vantage Analytics Library

To perform only recode 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; [...] ]
   recode_transformation'
);

optional_parameter

{ fallback = { true | false } |
  gensqlonly = { true | false } |
  index = column_name [,...] |
  indexunique = { true | false } |
  keycolumns = column_name [,...] |
  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
}

recode_transformation

recode = { transformation | { transformation } ... };

You must type the colored or bold braces.

transformation

recodevalues (recode_spec [,...]),
recodeother ({ SAME | NULL | literal }),
columns (column_spec [,...])

recode_spec

old_value/new_value

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 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

keycolumns

[Optional] The column or columns that form a unique key into the input and output tables of the transformation.

Required with nullstyle=true.

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'

recodevalues

The input table value or values to transform.

If you specify alias, it is the name of the output table column with the transformed value.

If you omit alias, the name of the output table column with the transformed value is value.

recodeother

The new value for each input table value that recodevalues does not specify.

Option	Description
SAME	Original value does not change.
NULL	Original value changes to NULL.
literal	Original value changes to literal.

columns

The input table column or columns to transform. Each column must have a numeric, character, or date data type. For a date column, precede column_name with the keyword DATE and do not enclose the date value in single quotation marks. Example: DATE 1995-12-26.

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)