# List of functions

The query editing tool allows you to use advanced functions to carry out complex filtering. To do this, the tool palette contains the Expression element that you can use in the workspace. Further information on this element is detailed in a specific section.

This element allows you to enter your conditions manually. Here you can use the functions defined in the following sections.

Several function types are available, depending on the desired results and the types of manipulated data:

• Dates
• Geomarketing
• Numerical values
• Other functions
• Aggregates
• String manipulation
• Sorting
NOTE

Additional functions are available in all the activities that allow you to use events variables after calling a workflow with external parameters. They are detailed in this section.

## Dates

The date functions are used to manipulate date or time values.

## Geomarketing

The geomarketing functions are used to manipulate geographical values.

 Name Description Syntax Distance Returns the distance in kilometers between two points defined by their longitude and latitude (expressed in degrees) Distance(, , , )

## Numerical

The numerical value functions are used to convert text to numbers.

 Name Description Syntax Abs Returns the absolute value of a number Abs() Ceil Returns the lowest integer greater than or equal to a number Ceil() Floor Returns the greatest integer lower than or equal to a number Floor() Greatest Returns the greater of two numbers Greatest(, ) Least Returns the smaller of two numbers Least(, ) Mod Returns the remainder of the integer division from n1 by n2 Mod(, ) Percent Returns the ratio of two numbers expressed as a percentage Percent(, ) Random Returns the random value Random() Round Rounds off a number to n decimals Round(, ) Sign Returns the sign of the number Sign() ToDouble Converts an integer to a float ToDouble() ToInt64 Converts a float to a 64 bit integer ToInt64() ToInteger Converts a float to an integer ToInteger() Trunc Truncates n1 to n2 decimals Trunc(, )

## Others

This table contains the remaining functions available.

 Name Description Syntax Case Returns value 1 if the condition is verified. Otherwise, returns value 2 Case(When(, ), Else()) ClearBit Deletes the Flag in the value ClearBit(, ) Coalesce Returns value 2 if value 1 is zero or null, otherwise returns value 1 Coalesce(, ) Decode Returns value 3 is value 1 = value 2, otherwise returns 4 Decode(, , , ) Else Returns value 1 (may only be used as a parameter of the case function) Else() GetEmailDomain Extracts the domain from an email address GetEmailDomain() GetMirrorURL Retrieves the URL of the mirror page server GetMirrorURL() Iif Returns value 1 if the expression is true, otherwise returns value 2 Iif(, , ) IsBitSet Indicates whether the Flag is in the value IsBitSet(, ) IsEmptyString Returns value 2 if the string is empty, otherwise returns value 3 IsEmptyString(, , ) NoNull Returns the empty string if the argument is NULL NoNull() RowId Returns the line number RowId SetBit Forces the Flag in the value SetBit(, ) ToBoolean Converts a number into a Boolean ToBoolean() When Returns value 1 if the expression is verified. Otherwise, returns value 2 (may only be used as a parameter of the case function) When(, ) newUUID Returns a new UUID. newUUID

## String

The string functions are used to manipulate a set of strings.

## Aggregates

The aggregation functions are only available when adding additional data from a workflow’s Query activity.

The aggregate functions are used to perform calculations on a set of values.

 Name Description Syntax Avg, Average Returns the average in a numerical column. Avg() Count, Count (except NULL) Counts the non-null values in a column. Count() CountAll, Count all Counts all of the values (including null values and duplicates). CountAll() Countdistinct, Distinct count Counts the non-null, distinct values in a column. Countdistinct() Max, Max Returns the maximum value in a numerical, string, or date column. Max() Min, Min Returns the minimum value in a numerical, string, or date column. Min() StringAgg, String aggregate Returns the concatenation of the values of a string type column, separated by the character in the second argument (default separator is comma). StringAgg(,) Sum, Sum Returns the sum of the values in a numerical column. Sum()

## Representation

The representation functions are used to order values.

 Name Description Syntax Desc Applies a descending sort Desc() OrderBy Sorts the result within the partition OrderBy() PartitionBy Partitions the result of a query on a table PartitionBy() RowNum Generates a line number based on the table partition and on a sorting sequence. This function is not supported for MySQL RowNum(PartitionBy(), OrderBy())