Selecting functions

The function list displays all available CLEM functions and operators. Scroll to select a function from the list, or, for easier searching, use the drop-down list to display a subset of functions or operators.

The following categories of functions are available:

Table 1. CLEM functions for use with your data
Function type	Description
Operators	Lists all the operators you can use when building expressions. Operators are also available from the buttons.
Information	Used to gain insight into field values. For example, the function `is_string` returns `true` for all records whose type is a string.
Conversion	Used to construct new fields or convert storage type. For example, the function `to_timestamp` converts the selected field to a timestamp.
Comparison	Used to compare field values to each other or to a specified string. For example, `<=` is used to compare whether the values of two fields are lesser or equal.
Logical	Used to perform logical operations, such as `if, then, else` operations.
Numeric	Used to perform numeric calculations, such as the natural log of field values.
Trigonometric	Used to perform trigonometric calculations, such as the arccosine of a specified angle.
Probability	Returns probabilities that are based on various distributions, such as probability that a value from Student's t distribution is less than a specific value.
Spatial Functions	Used to perform spatial calculations on geospatial data.
Bitwise	Used to manipulate integers as bit patterns.
Random	Used to randomly select items or generate numbers.
String	Used to perform various operations on strings, such as `stripchar`, which allows you to remove a specified character.
Date and time	Used to perform various operations on date, time, and timestamp fields.
Sequence	Used to gain insight into the record sequence of a data set or perform operations that are based on that sequence.
Global	Used to access global values that are created by a Set Globals node. For example, `@MEAN` is used to refer to the mean average of all values for a field across the entire data set.
Blanks and Null	Used to access, flag, and frequently fill user-specified blanks or system-missing values. For example, `@BLANK(FIELD)` is used to raise a true flag for records where blanks are present.
Special Fields	Used to denote the specific fields under examination. For example, `@FIELD` is used when deriving multiple fields.

After you select a group of functions, double-click to insert the functions into the Expression box at the point indicated by the position of the cursor.

Database functions

You can run an SPSS Modeler desktop stream file (.str) that contains database functions. But they aren't yet available in the Expression Builder user interface.

Database functions can be listed in many different locations. The following table shows the locations that SPSS Modeler searches when looking for function details. This table can be used by database administrators to ensure that users have access privileges to the required areas to they can use the different functions.

Note: If using database functions from Amazon Redshift, your database administrator may need to grant you permissions to the following six database objects. The first four are system catalog tables, and the last two are schemas.

pg_type
pg_proc
pg_namespace
pg_aggregate
information_schema
pg_catalog

Table 2. Database functions in the Expression Builder
Database	Function type	Where to find functions	Conditions used to filter functions
Db2 LUW	UDF	SYSCAT.ROUTINES SYSCAT.ROUTINEPARMS	`ROUTINETYPE` is `F` and `FUNCTIONTYPE` is `S`
Db2 LUW	UDA	SYSCAT.ROUTINES SYSCAT.ROUTINEPARMS	`ROUTINETYPE` is `F` and `FUNCTIONTYPE` is `C`
Db2 iSeries	UDF	QSYS2.SYSROUTINES QSYS2.SYSPARMS	`ROUTINE_TYPE` is `F` and `FUNCTION_TYPE` is `S`
Db2 iSeries	UDA	QSYS2.SYSROUTINES QSYS2.SYSPARMS	`ROUTINE_TYPE` is `F` and `FUNCTION_TYPE` is `C`
Db2 z/OS	UDF	SYSIBM.SYSROUTINES SYSIBM.SYSPARMS	`ROUTINETYPE` is `F` and `FUNCTIONTYPE` is `S`
Db2 z/OS	UDA	SYSIBM.SYSROUTINES SYSIBM.SYSPARMS	`ROUTINETYPE` is `F` and `FUNCTIONTYPE` is `C`
SQL Server	UDF	SYS.ALL_OBJECTS SYS.ALL_PARAMETERS SYS.TYPES	`TYPE` is either `FN` or `FS`
SQL Server	UDA	SYS.ALL_OBJECTS SYS.ALL_PARAMETERS SYS.TYPES	`TYPE` is `AF`
Oracle	UDF	ALL_ARGUMENTS ALL_PROCEDURES	All of the following conditions are satisfied: `OBJECT_TYPE` is `FUNCTION` `AGGREGATE` is `NO` `PLS_TYPE` is not `NULL`
Oracle	UDA	ALL_ARGUMENTS ALL_PROCEDURES	All of the following conditions are satisfied: `ARGUMENT_NAME` is `NULL` `AGGREGATE` is `YES` `PLS_TYPE` is not `NULL`
Teradata	UDF	DBC.FUNCTIONS DBC.ALLRIGHTS	All of the following conditions are satisfied: `FUNCTIONTYPE` is `F` `COLUMNNAME` is `RETURN0` `SPPARAMETERTYPE` is `O` `ACCESSRIGHT` is `EF`
Teradata	UDA	DBC.FUNCTIONS DBC.ALLRIGHTS	All of the following conditions are satisfied: `FUNCTIONTYPE` is `A` `COLUMNNAME` is `RETURN0` `SPPARAMETERTYPE` is `O` `ACCESSRIGHT` is `EF`
Netezza	UDF	####.._V_FUNCTION NZA.._V_FUNCTION INZA.._V_FUNCTION	For ####.._V_FUNCTION, the following conditions apply: `RESULT` does not contain a string with values such as: `TABLE%` `FUNCTION` does not contain a string with values such as: `'/_%' escape '/'` `VARARGS` is `FALSE` For both NZA.._V_FUNCTION and INZA.._V_FUNCTION, the following conditions apply: `RESULT` does not contain a string with values such as: `TABLE%` `FUNCTION` does not contain a string with values such as: `'/_%' escape '/'` `BUILTIN` is `f` `VARARGS` is `FALSE`
Netezza	UDA	####.._V_AGGREGATE NZA.._V_FUNCTION INZA.._V_FUNCTION	Both of the following conditions are satisfied: `AGGTYPE` is `ANY` or `GROUPED` `VARARGS` is `FALSE`
Netezza	WUDA	####.._V_AGGREGATE NZA.._V_FUNCTION INZA.._V_FUNCTION	For ####.._V_AGGREGATE, the following conditions apply: `AGGTYPE` is `ANY` or `ANALYTIC` `AGGREGATE` is not `MAX_LABEL` `VARARGS` is `FALSE` For both NZA.._V_FUNCTION and INZA.._V_FUNCTION, the following conditions apply: `AGGTYPE` is `ANY` or `ANALYTIC` `BUILTIN` is `f` `VARARGS` is `FALSE`

The following acronyms and symbols are used in the table:

UDF: User Defined Function
UDA: User Defined Aggregate Function
WUDA: User Defined Aggregate Function
####: The database you're currently connected to.