SQL Expressions - Revision history

Ollvihe: /* String functions */

2026-06-26T11:35:08Z

String functions

← Older revision		Revision as of 11:35, 26 June 2026
Line 405:		Line 405:
	Returns: false		Returns: false

	Regexp("1234", "\d")		Regexp("1234", "\\d")
	Returns: false		Returns: false

Ollvihe: /* Window functions */

2026-06-21T18:55:00Z

Window functions

← Older revision		Revision as of 18:55, 21 June 2026
Line 734:		Line 734:
	\|-		\|-
	\|\|ArrayAgg		\|\|ArrayAgg
	\|\|~~Returns the aggregated~~ values as an array. If the input ~~is empty~~, the function returns an empty array. This function is similar to the ListAgg function, except the type of the returned data is an array (not a string).		\|\|Aggregates values by returning an array containing all the items. If the input has no rows, the function returns an empty array. This function is similar to the ListAgg function, except the type of the returned data is an array (not a string).

	Parameters:		Parameters:

Ollvihe: /* Other functions */

2026-06-21T18:51:34Z

Other functions

← Older revision		Revision as of 18:51, 21 June 2026
Line 1,181:		Line 1,181:
	NullIf(null, 1) // => null		NullIf(null, 1) // => null
	NullIf(1, null) // => 1		NullIf(1, null) // => 1
			</pre>

			Example: Avoid dividing by zero - the following expression returns null if Column("B") is zero:
			<pre>
			Column("A") / NullIf(Column("B"), 0)
	</pre>		</pre>
	\|-		\|-

Ollvihe: /* Aggregation operations */

2026-06-21T18:47:03Z

Aggregation operations

← Older revision		Revision as of 18:47, 21 June 2026
Line 1,330:		Line 1,330:
	** '''Ordering''': Array of columns defining how items are ordered.		** '''Ordering''': Array of columns defining how items are ordered.
	** '''Distinct''': Whether duplicate items are removed. Default value is ''false''. When showing distinct items, the items are sorted by their values.		** '''Distinct''': Whether duplicate items are removed. Default value is ''false''. When showing distinct items, the items are sorted by their values.
	* '''MinBy''': Finds a row containing the minimum value for a column and returns the value of another column in that row. The aggregation has following parameter:		* '''MinBy''': Finds a row containing the minimum value for the defined column and returns the value of another column in that row. The aggregation has the following parameter:
	** '''Ordering''': One column in an array defining from ~~where~~ the ~~minumum~~ is ~~defined~~.		** '''Ordering''': One column in an array defining from which column the minimum is determined.
	Can be defined as an object as follows: <pre>#{ "Function": "MinBy", "Ordering": ["Cost"] }</pre>		Can be defined as an object as follows: <pre>#{ "Function": "MinBy", "Ordering": ["Cost"] }</pre>
	* '''MaxBy''': Finds a row containing the maximum value for a column and returns the value of another column in that row. The aggregation has following parameter:		* '''MaxBy''': Finds a row containing the maximum value for the defined column and returns the value of another column in that row. The aggregation has the following parameter:
	** '''Ordering''': One column in an array defining from ~~where~~ the maximum is ~~defined~~.		** '''Ordering''': One column in an array defining from which column the maximum is determined.
	Can be defined as an object as follows: <pre>#{ "Function": "MaxBy", "Ordering": ["Cost"] }</pre>		Can be defined as an object as follows: <pre>#{ "Function": "MaxBy", "Ordering": ["Cost"] }</pre>

Ollvihe: /* Aggregation operations */

2026-06-18T13:00:43Z

Aggregation operations

← Older revision		Revision as of 13:00, 18 June 2026
Line 1,330:		Line 1,330:
	** '''Ordering''': Array of columns defining how items are ordered.		** '''Ordering''': Array of columns defining how items are ordered.
	** '''Distinct''': Whether duplicate items are removed. Default value is ''false''. When showing distinct items, the items are sorted by their values.		** '''Distinct''': Whether duplicate items are removed. Default value is ''false''. When showing distinct items, the items are sorted by their values.
			* '''MinBy''': Finds a row containing the minimum value for a column and returns the value of another column in that row. The aggregation has following parameter:
			** '''Ordering''': One column in an array defining from where the minumum is defined.
			Can be defined as an object as follows: <pre>#{ "Function": "MinBy", "Ordering": ["Cost"] }</pre>
	* '''MaxBy''': Finds a row containing the maximum value for a column and returns the value of another column in that row. The aggregation has following parameter:		* '''MaxBy''': Finds a row containing the maximum value for a column and returns the value of another column in that row. The aggregation has following parameter:
	** '''Ordering''': One column in an array defining from where the maximum is defined.		** '''Ordering''': One column in an array defining from where the maximum is defined.
	Can be defined as an object as follows: <pre>#{ "Function": "MaxBy", "Ordering": ["Cost"] }</pre>		Can be defined as an object as follows: <pre>#{ "Function": "MaxBy", "Ordering": ["Cost"] }</pre>
	* '''MinBy''': Finds a row containing the minimum value for a column and returns the value of another column in that row. The aggregation has following parameter:
	** '''Ordering''': One column in an array defining from where the minumum is defined.
	~~Can be defined as an object as follows: <pre>#{ "Function": "MinBy", "Ordering": ["Cost"] }</pre>~~

Ollvihe: /* Aggregation operations */

2026-06-16T23:04:18Z

Aggregation operations

Ollvihe: /* Aggregation operations */

2026-06-16T23:03:31Z

Aggregation operations

← Older revision		Revision as of 23:03, 16 June 2026
Line 1,328:		Line 1,328:
	** '''Ordering''': Array of columns defining how items are ordered.		** '''Ordering''': Array of columns defining how items are ordered.
	** '''Distinct''': Whether duplicate items are removed. Default value is ''false''. When showing distinct items, the items are sorted by their values.		** '''Distinct''': Whether duplicate items are removed. Default value is ''false''. When showing distinct items, the items are sorted by their values.
	* '''MaxBy''': Finds a row containing the maximum value for a column and returns the value of another column in that row. ~~Can be defined as an object as follows: <pre>#{ "Function": "MaxBy", "Ordering": ["Cost"] }</pre>~~The aggregation has following parameter:		* '''MaxBy''': Finds a row containing the maximum value for a column and returns the value of another column in that row. The aggregation has following parameter:
	** '''Ordering''': One column in an array defining from where the maximum is defined.		** '''Ordering''': One column in an array defining from where the maximum is defined.
			Can be defined as an object as follows: <pre>#{ "Function": "MaxBy", "Ordering": ["Cost"] }</pre>
	* '''MinBy''': Finds a row containing the minimum value for a column and returns the value of another column in that row. The aggregation has following parameter:		* '''MinBy''': Finds a row containing the minimum value for a column and returns the value of another column in that row. The aggregation has following parameter:
	** '''Ordering''': One column in an array defining from where the minumum is defined.		** '''Ordering''': One column in an array defining from where the minumum is defined.

Ollvihe: /* Aggregation operations */

2026-06-16T23:02:47Z

Aggregation operations

← Older revision		Revision as of 23:02, 16 June 2026
Line 1,328:		Line 1,328:
	** '''Ordering''': Array of columns defining how items are ordered.		** '''Ordering''': Array of columns defining how items are ordered.
	** '''Distinct''': Whether duplicate items are removed. Default value is ''false''. When showing distinct items, the items are sorted by their values.		** '''Distinct''': Whether duplicate items are removed. Default value is ''false''. When showing distinct items, the items are sorted by their values.
	* '''MaxBy''': Finds a row containing the maximum value for a column and returns the value of another column in that row. ~~This~~ aggregation has following parameter:		* '''MaxBy''': Finds a row containing the maximum value for a column and returns the value of another column in that row. Can be defined as an object as follows: <pre>#{ "Function": "MaxBy", "Ordering": ["Cost"] }</pre>The aggregation has following parameter:
	** '''Ordering''': One column in an array defining from where the maximum is defined.		** '''Ordering''': One column in an array defining from where the maximum is defined.
	* '''MinBy''': Finds a row containing the minimum value for a column and returns the value of another column in that row. ~~This~~ aggregation has following parameter:		* '''MinBy''': Finds a row containing the minimum value for a column and returns the value of another column in that row. The aggregation has following parameter:
	** '''Ordering''': One column in an array defining from where the minumum is defined.		** '''Ordering''': One column in an array defining from where the minumum is defined.

Ollvihe at 22:59, 16 June 2026

2026-06-16T22:59:48Z

← Older revision		Revision as of 22:59, 16 June 2026
Line 748:		Line 748:
	\|-		\|-
	\|\|ListAgg		\|\|ListAgg
	\|\|Returns the aggregated values as a string list where each item is separated by the defined ~~limiter~~. This function is similar to the ArrayAgg function, except the type of the returned data is a string (not an array).		\|\|Returns the aggregated values as a string list where each item is separated by the defined delimiter. This function is similar to the ArrayAgg function, except the type of the returned data is a string (not an array).

	Parameters:		Parameters:
Line 1,328:		Line 1,328:
	** '''Ordering''': Array of columns defining how items are ordered.		** '''Ordering''': Array of columns defining how items are ordered.
	** '''Distinct''': Whether duplicate items are removed. Default value is ''false''. When showing distinct items, the items are sorted by their values.		** '''Distinct''': Whether duplicate items are removed. Default value is ''false''. When showing distinct items, the items are sorted by their values.
			* '''MaxBy''': Finds a row containing the maximum value for a column and returns the value of another column in that row. This aggregation has following parameter:
			** '''Ordering''': One column in an array defining from where the maximum is defined.
			* '''MinBy''': Finds a row containing the minimum value for a column and returns the value of another column in that row. This aggregation has following parameter:
			** '''Ordering''': One column in an array defining from where the minumum is defined.

Ollvihe at 22:50, 16 June 2026

2026-06-16T22:50:52Z

← Older revision		Revision as of 22:50, 16 June 2026
Line 1,102:		Line 1,102:
	\|\|IfNull		\|\|IfNull
	\|\|		\|\|
	If the first parameter is null, returns the second parameter, otherwise returns the first parameter.		If the first parameter is null, returns the second parameter, otherwise returns the first parameter. Functionality is the same as in the ''Coalesce'' function.

	Examples:		Examples:

← Older revision		Revision as of 23:04, 16 June 2026
Line 1,314:		Line 1,314:
	* '''Count''': Number of items. Can be used for any type of data.		* '''Count''': Number of items. Can be used for any type of data.
	* '''CountDistinct''': Number of unique items (i.e., duplicates are removed). Can be used for any type of data.		* '''CountDistinct''': Number of unique items (i.e., duplicates are removed). Can be used for any type of data.
	* '''Percentile''': Percentile value. Can be used for numbers. ~~Can be defined as an object as follows: <pre>#{ "Function": "Percentile", "Percentile": 0.75 }</pre>~~Percentile aggregation has following parameter:		* '''Percentile''': Percentile value. Can be used for numbers. Percentile aggregation has following parameter:
	** '''Percentile''': Percentile value between 0 and 1.		** '''Percentile''': Percentile value between 0 and 1.
			Can be defined as an object as follows: <pre>#{ "Function": "Percentile", "Percentile": 0.75 }</pre>
	* '''Stdev''': Sample standard deviation. Can be calculated for numbers.		* '''Stdev''': Sample standard deviation. Can be calculated for numbers.
	* '''Stdevp''': Population standard deviation. Can be calculated for numbers.		* '''Stdevp''': Population standard deviation. Can be calculated for numbers.
	* '''Var''': Sample variance. Can be used for numbers.		* '''Var''': Sample variance. Can be used for numbers.
	* '''Varp''': Population variance. Can be used for numbers.		* '''Varp''': Population variance. Can be used for numbers.
	* '''List''': Combines textual values into a single list in defined order and separated by defined characters. ~~Can be defined as an object as follows: <pre>#{ "Function": "List", "Ordering": [EventType], "Separator": ", ", "Distinct": true }</pre>~~List aggregation has following parameters:		* '''List''': Combines textual values into a single list in defined order and separated by defined characters. List aggregation has following parameters:
	** '''Ordering''': Array of columns defining how items are ordered. Ordering is supported only when ''Distinct'' is ''false''.		** '''Ordering''': Array of columns defining how items are ordered. Ordering is supported only when ''Distinct'' is ''false''.
	** '''Separator''': Characters separating the combined items.		** '''Separator''': Characters separating the combined items.
	** '''Distinct''': Whether duplicate items are removed. Default value is ''false''. When showing distinct items, the items are sorted by their values.		** '''Distinct''': Whether duplicate items are removed. Default value is ''false''. When showing distinct items, the items are sorted by their values.
			Can be defined as an object as follows: <pre>#{ "Function": "List", "Ordering": [EventType], "Separator": ", ", "Distinct": true }</pre>
	* '''Any''': Returns any value of the aggregated values. This is the most efficient aggregation when it's known that there is maximum of one value among the aggregated values. Note that there is no guarantee that the returned value will be the same every time.		* '''Any''': Returns any value of the aggregated values. This is the most efficient aggregation when it's known that there is maximum of one value among the aggregated values. Note that there is no guarantee that the returned value will be the same every time.
	* '''Array''': Returns the aggregated values as a single array object. This aggregation has following parameters:		* '''Array''': Returns the aggregated values as a single array object. This aggregation has following parameters:
Line 1,333:		Line 1,335:
	* '''MinBy''': Finds a row containing the minimum value for a column and returns the value of another column in that row. The aggregation has following parameter:		* '''MinBy''': Finds a row containing the minimum value for a column and returns the value of another column in that row. The aggregation has following parameter:
	** '''Ordering''': One column in an array defining from where the minumum is defined.		** '''Ordering''': One column in an array defining from where the minumum is defined.
			Can be defined as an object as follows: <pre>#{ "Function": "MinBy", "Ordering": ["Cost"] }</pre>