Database functions

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 be able to use the different functions.

In addition, the table lists the conditions that are used to filter when a function is available for use, based on the database and function type.

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

Key to terms used in the table

  • UDF User Defined Function
  • UDA User Defined Aggregate Function
  • WUDA User Defined Window Aggregate Function
  • #### the database that you are currently connected to.