Syntax | Design Code Function | Vantage Analytics Library - Syntax - Vantage Analytics Library

To perform only design code 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; [...] ]
   designcode_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
}

designcode_transformation

designcode = { transformation | { transformation } ... };

You must type the colored or bold braces.

transformation

designstyle ({ dummycode | contrastcode, ref_value }),
designvalues (value_spec [,...] | all),
columns (column_spec)

value_spec

value[/alias]

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

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'

designstyle

One of the following design styles and its parameters, if any.

Style	Description
dummycode	Produces a new column for each value specified by designvalues, with value 0 or 1, depending on whether the original column assumes the design value.
contrastcode, ref_value	Produces new column for each value specified by designvalues, with a value of 0 or 1 depending on whether original column assumes value, or value -1 if original value equals ref_value. If original column assumes n values, transformation creates n-1 or fewer new columns.

designvalues

The value to code.

If you specify alias, it becomes the name of the coded value column in the output table.

Example: designvalues (M/Males, F/Females)

Output column names: Males and Females

If you omit an alias, the name of the coded value column in the output table is designvalue +_+ column name.

Example: designvalues (M,F)

Output column names M_gender and F_gender

If you specify the keyword all and no other value, the database is queried to get the distinct values of the input column, each of which becomes a design column. Do not use this option with a column containing too many values, which can cause the SQL to fail.

Example: designvalues(all), is equivalent to: designvalues(M,F)

If a literal design value in the designvalues parameter contains a character that is invalid, such as a forward slash, the value must be enclosed in pairs of single quote characters.

Example: designvalues (''Rain / Shine'')

This example contains four single quotes around Rain / Shine.

columns

The input table column 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.

If you specify a transformed_column_name (column alias), the variable becomes the name of the transformed column in the output table, combined with the designvalues or aliases. If you omit a transformed_column_name, the name of the transformed column in the output table is column_name, again combined with the designvalues or aliases.

Design value aliases take precedence over transformed column names if both are present. For example, the following parameters result in output columns named males and females.

designvalues(M/males,F/females),columns(gender/gen)

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)