Formulas in Queries

Arrays

These functions are special in that they handle arrays or ordered sets of data as arguments. They are mainly used to handle the results of subqueries or lists of filter values.

Contains the value

Syntax: contains(Array, value)

Checks if value is contained in the set of elements in Array form, as for example happens with the result of a subquery. Returns true if the expression is part of the array or false otherwise.

Example:

Array element

Syntax: elementAt(Array, index)

Returns the value or element of the array located at the ""index"" position. Note that an array of N elements has indexes from 0 to N-1, if the index is greater than N-1 it is returned empty.

Example:

Value position

Syntax: indexOfArray(Array, value)

Checks if value is contained in the set of elements in Array form, as for example happens with the result of a subquery. Returns the number equal to the index of the element equal to value if the expression is part of the array or -1 otherwise. Note that an array of N elements has indexes from 0 to N-1.

Example:

Array size

Syntax: size(Array)

Returns a numeric value equal to the number of elements or size of the Array. All elements in the array must be of the same type.

Examples:

Comparison (equality)

Functions

Comparison

Syntax: cmp(value1, value2)

Compares the type and value of the two arguments:

• If the types are equal it returns 1 if the first value is greater than the second, -1 if less and 0 if they are equal.
• If the types are different, it follows the order criteria from lowest to highest: null, number, text, array, boolean (true, false) and date.

Equal

Syntax: eq(value1, value2)

Compares two values against each other, and returns true if the two values and their types are equal, and false otherwise.

Example:

Not equal

Syntax: ne(value1, value2)

Compares two values against each other, and returns true if the two values or their types are different, and false otherwise.

Example:

Comparison (greater/lower)

Greater or equal

Syntax: gte(value1, value2)

Compares two values against each other, and returns true if the first value is greater than or equal to the second and their types are equal, and false otherwise.

Examples

Greater

Syntax: gt(value1, value2)

Compares two values against each other, and returns true if the first value is greater than the second and their types are equal, and false otherwise.

Examples:

Lower or equal

Syntax: lte(value1, value2)

Compares two values with each other, and returns true if the first value is less than or equal to the second and their types are equal, and false otherwise..

Examples:

Lower

Syntax: lt(value1, value2)

Compares two values against each other, and returns true if the first value is less than the second and their types are equal, and false otherwise.

Examples:

Conditionals

Conditional functions, which are very frequently used, allow you to change the conditions for the transformation of the original fields. They are the following:

Simple conditional

Syntax: cond(boolean, value1, value2)

The first argument is a boolean (true or false), for example the result of a comparison function. If boolean equals true the function returns value1, otherwise value2. In addition, value1 and value2 must necessarily have the same data type.

Examples:

Multiple conditional

Syntax: switch(boolean1, value1; boolean2, value2; ...; value)

The function receives pairs of values (boolean, value) separated by "";"". If boolean1 is equal to true the function returns value1, otherwise it goes to the next pair; if boolean2 is equal to true the function returns value2, otherwise it goes to the next pair; this process is repeated as many times as pairs (boolean, value) are indicated; finally, if all boolean are false, value is returned. Particularities:

• At least 2 conditions must be defined, otherwise the simple conditional function must be used.
• The default value is not mandatory, a null value is returned if not defined.

Examples:

If null

Syntax: ifNull(value, replacement)

The ifNull function allows to replace the null values of a column by another value. If value has a null value, the function returns replacement, otherwise value. It is mandatory that replacement has the same data type as value.

Examples:

Conversion

Conversion functions allow you to change the type of a field. This process is usually delicate, especially when there are decimals, punctuation marks or special formats. It is advisable to test and validate that everything works correctly when using these functions.

Number to text

Syntax: toString(number)

Converts the number value to text. Integers are returned without decimal places, and numbers with decimal places are returned with a maximum of 5 decimal places. In addition, from 10 integer digits onwards the number is formatted as an exponential.

Examples:

Text to number

Syntax: toNumber(text)

Convert the string text to a number, whenever possible.

Examples:

Dates (parts)

These functions allow you to create, transform or convert fields of a temporary nature.

Functions

Year

Syntax: year(date [, boolean])

The function extracts the year from the date value. If the boolean true is given as the second argument, the return of the function is numeric, while otherwise or when only the first argument is given, the return is of text type.

Examples:

Month

Syntax: month(date [, boolean])

The function extracts the month from the date value. If the boolean true is given as the second argument the return of the function is numeric (1-12), while otherwise or when only the first argument is given the return is of text type (01-12).

Examples:

Name of the month

Syntax: monthName(date [, language])

The function extracts the month from the date value and returns the month name (""January"", ""February"", ...). The language of the text is determined according to the following priority criteria: language in which the application is consumed (multilingual) if defined, default language of the application (multilingual) if no specific language is specified, default language of the user in Vision (non-multilingual application), default language of the browser where the application is consumed (non-multilingual).

Optionally it receives a second parameter indicating the language used if this is to be set. To change the language, the parameter must be passed using the two-letter ISO code for the language (""en"", ""pt"", ""es"").

Examples:

Day of the month

Syntax: day(date [, boolean])

The function extracts the day of the month from the date value. If the boolean true is given as the second argument the return of the function is numeric (1-31), while otherwise or when only the first argument is given the return is text (01-31).

Examples:

Name of the day of the week

Syntax: dayName(date [, language])

The function extracts the day of the week from the date value and returns its name ("Monday", "Tuesday", ...). The language of the text is determined according to the following priority criteria: language in which the application is consumed (multilingual) if defined, default language of the application (multilingual) if no specific language is specified, default language of the user in Vision (non-multilingual application), default language of the browser where the application is consumed (non-multilingual).

Optionally it receives a second parameter indicating the language used if this is to be set. To change the language, the parameter must be passed using the two-letter ISO code for the language ("en", "pt", "es").

Examples:

Quarter

Syntax: quarter(date [, prefix])

The function extracts the quarter of the year from the date value (1-4), and returns it as text. Optionally it can receive a text in quotes as a second parameter, which will be used as a prefix.

Examples:

Dates (complete)

Estas funciones permiten crear, transformar o convertir campos de naturaleza temporal.

Functions:

Date to text

Syntax: dateToString(date, format)

It converts the date to text in the specified format.

Format options.

Examples:

Date from text

Syntax: dateFromString(text)

Returns a date based on the value specified in the text string, which must be constructed using the format “YYYY-MM-DD H24: MI:SS.sss.” The year and month must be provided; the rest of the format is optional.

Examples:

Date by components

Syntax: dateFromParts(YYYY[, MM, DD, H24, MI, SS, sss])

Returns a date composed of its parts: year, month, day, hour (24-hour format), minute, second, and millisecond. At least the year must be specified.

Examples:

Logic

Logical functions perform a logical operation with their arguments, of type boolean. Their return is in turn of Boolean type (true/false), so they are not used directly as the result of a formula, but as variables in other functions that require Booleans as variables.

Function "Or"

Syntax: or(boolean1, boolean2, ...)

Returns true if any of the arguments is true, and false otherwise.

Examples:

Function "No"

Syntax: not(boolean)

It performs the "negation" operation: if the argument is true it returns false, and if false it returns true.

Examples:

Function "And"

Syntax: and(boolean1, boolean2, ...)

Returns true if all arguments are true, and false otherwise.

Examples:

Mathematics (approximation)

These commonly used mathematical functions apply only to numeric values and return a number.Functions

Upper integer

Syntax: ceil(number)

Returns the smallest integer that is greater than or equal to the given number.

Examples:

Lower integer

Syntax: floor(number)

Devuelve el entero más grande que sea menor o igual al número dado.

Examples:

Integer

Syntax: trunc(number)

Trunca el número dado a su valor entero: el entero inferior si es positivo y el superior si es negativo.

Ejemplos:

Rounding decimals

Syntax: round(number[, precision])

Redondea number a la precisión (accuracy) decimal indicada, y si no se indica ésta explícitamente se entiende como 0. Cuando redondea a valores enteros, el valor X.5 lo redondea a X si X es par, y a X+1 si X es impar, mientras que cuando se redondea a uno o más decimales, el valor X.Y5 lo redondea siempre a X.Y (cuestiones de precisión en la representación de los valores numéricos decimales).

Examples:

Mathematics (general)

Commonly used mathematical functions, apply only to numeric values and return a number.

Functions:

Square root

Syntax: sqrt(number)

Calcula la raíz cuadrada de number.

Examples:

Absolute value

Syntax: abs(number)

Devuelve el valor absoluto de number.

Examples:

Mathematics (exponential and logarithms)

These mathematical functions allow you to obtain exponentials and logarithms.

Functions:

Exponential

Syntax: exp(number)

Raises the number e to the exponent number (e^number).

Examples:

Logarithm

Syntax: log(number, base)

Calculates the Logarithm of number in the given base.

Examples:

Natural logarithm

Syntax: ln(number)

Calculate the natural logarithm of number.

Examples:

Operators

Operators apply only when they are placed between numbers. They do not have a function syntax with arguments, simply the operator goes in the middle of the 2 numbers or components.

Functions

Sum (+)

Syntax: addend1 + addend2

Calculates the sum of two numbers or of a number and a date. If one of the expressions is of type date the other expression is treated as the number of milliseconds to add to the date.

The return type of the formula is numeric if the addends are numbers, or a date if one of the addends is of type date.

Examples:

Subtraction (-)

Syntax: minuend - subtrahend

Calculates the subtraction of two numbers, of a date minus a number, or of two dates. When subtracting a number from a date the number is interpreted in milliseconds, and when subtracting 2 dates both are converted to milliseconds to proceed to a numerical subtraction.

The return type of the formula is numeric if minuend and subtrahend are numbers, is a date if minuend is a date and subtrahend is a number, and a number equal to the difference in milliseconds if minuend and subtrahend are both dates.

Examples:

Multiplication (*)

Syntax: factor1 * factor2

Calculates the multiplication of two numbers, returning a number.

Examples:

Division (/)

Syntax: dividend / divisor

Calculates the division of two numbers, returning a number. When divisor takes the value 0 the result is null, as well as when either dividend or divisor is null.

Examples:

Power (^)

Syntax: base ^ power

Raises the base number to the specified numeric power exponent, returning a base ^power number.

Examples:

Subtraction of division (%)

Syntax: dividend % divisor

Calculates the remainder of the division of two numbers, returning a number. When divisor takes the value 0 the result is null, as well as when either dividend or divisor is null.

Examples:

Text (general)

These functions allow the manipulation, transformation and conversion of text strings. They are as follows:

Functions

String comparison

Syntax: strCaseCmp(text1, text2)

Given two text arguments, an alphanumeric comparison is performed, ignoring capital letters. The return value is numeric:

• 1 if text1 is greater than text2.
• 0 if both texts are equal.
• -1 if text2 is greater than text1.

Examples:

Concatenation

Syntax: concat(text1, text2, ...)

Concatenates two or more text strings.

Examples:

Length

Syntax: strLen(text)

Returns the number of characters (strictly the number of UTF-8 code points) in the string text.

Examples:

Position

Syntax: indexOf(text, string [, from, to])

This function returns the position of the first string match in text, where the position starts with the value 0 and ends with the value L-1, where L is the number of characters in text (strLen(text)). In case of no match it returns the value -1.

Optionally, you can narrow the search range, that is, search for a match only in the range [from, to] of characters of text, discarding any match outside that range.

Examples:

Separate text

Syntax: split(text, sep)

It splits the text string using the sep separator and returns an array with as many elements as divisions are obtained.

Examples:

Text (change)

Estas funciones permiten la transformación y conversión de de cadenas de texto. Son las siguientes:

Functions

Uppercase

Syntax: toUpper(text)

Converts all text letters to uppercase. Applies the conversion to ASCII characters only.

Examples:

Lowercase

Syntax: toLower(text)

Converts all text letters to lowercase. Applies the conversion to ASCII characters only.

Examples:

Trim

Syntax: trim(text, [string])

Removes all "space" characters at the beginning and end of text. In case of passing a second argument string it removes all characters that are part of string.

Examples:

Substring

Syntax: substr(text, from, length)

Gets the substring of text from the from position by taking length characters, taking into account that the positions start counting from 0.

Examples:

Trigonometric (general)

Trigonometric functions are mathematical functions specifically for use with angle-related measurements. They are as follows:

Functions:

Degrees to radians

Syntax: degreesToRadians(number)

Makes the conversion from number degrees to radians:1° × ?/180 = 0.0174533... rad.

Examples:

Radians to degrees

Syntax: radiansToDegrees(number)

Makes the conversion from number radians to degrees:1 rad × 180/? = 57.2958...°.

Examples:

Sine

Syntax: sin(number)

Obtains the sine of number, understanding the value in radians.

Examples:

Cosine

Syntax: cos(number)

Obtains the cosine of number, understanding the value in radians.

Examples:

Tangent

Syntax: tan(number)

Obtains the tangent of number, understanding the value in radians.

Examples:

Trigonometric (arcsine)

Functions:

Arcsine

Syntax: asin(number)

Gets the arcsine of number, provided that it is a number greater than or equal to -1 and less than or equal to 1.

Examples:

Hyperbolic Arcsine

Syntax: asinh(number)

Obtains the hyperbolic arcsine of number.

Examples:

Trigonometric (arccosine)

Functions:

Arccosine

Syntax: acos(number)

Gets the arccosine of number, provided that it is a number greater than or equal to -1 and less than or equal to 1.

Examples:

Hyperbolic Arccosine

Syntax: acosh(number)

Obtains the hyperbolic arccosine of number, provided that it is a number greater than or equal to 1.

Examples:

Trigonometric (arctangent)

Functions:

Arctangent

Syntax: atan(number)

Gets the arctangent of number, returning a value in radians.

Examples:

2-parameter Arctangent

Syntax: atan2(number1, number2)

Obtains the arctangent of 2 parameters number1 and number2, that is, the arctangent of number1/number2.

Examples:

Hyperbolic Arctangent

Syntax: atanh(number)

Obtains the hyperbolic arctangent of number, provided that it is a number greater than -1 and less than 1.

Examples: