SQL optimization (SPSS Modeler) | IBM Cloud Pak for Data as a Service

CLEM expressions and operators supporting SQL pushback

Last updated: Jan 17, 2024

SQL optimization (SPSS Modeler)

The tables in this section list the mathematical operations and expressions that support SQL generation and are often used during data mining. Operations absent from these tables don't support SQL generation.

Table 1. Operators
Operations supporting SQL generation	Notes
`+`
`-`
`/`
`*`
`><`	Used to concatenate strings.

Table 2. Relational operators
Operations supporting SQL generation	Notes
`=`
`/=`	Used to specify "not equal."
`>`
`>=`
`<`
`<=`

Table 3. Functions
Operations supporting SQL generation	Notes
`abs`
`allbutfirst`
`allbutlast`
`and`
`arccos`
`arcsin`
`arctan`
`arctanh`
`cos`
`div`
`exp`
`fracof`
`hasstartstring`
`hassubstring`
`integer`
`intof`
`isaplhacode`
`islowercode`
`isnumbercode`
`isstartstring`
`issubstring`
`isuppercode`
`last`
`length`
`locchar`
`log`
`log10`
`lowertoupper`
`max`
`member`
`min`
`negate`
`not`
`number`
`or`
`pi`
`real`
`rem`
`round`
`sign`
`sin`
`sqrt`
`string`
`strmember`
`subscrs`
`substring`
`substring_between`
`uppertolower`
`to_string`

Table 4. Special functions
Operations supporting SQL generation	Notes
`@NULL`
`@GLOBAL_AVE`	You can use the special global functions to retrieve global values computed by the Set Globals node.
`@GLOBAL_SUM`
`@GLOBAL_MAX`
`@GLOBAL_MEAN`
`@GLOBAL_MIN`
`@GLOBALSDEV`

Table 5. Aggregate functions
Operations supporting SQL generation	Notes
`Sum`
`Mean`
`Min`
`Max`
`Count`
`SDev`