GlideDBFunctionBuilder - Scoped, Global
-
- UpdatedJan 30, 2025
- 10 minutes to read
- Yokohama
- API reference
The GlideDBFunctionBuilder API provides methods to build functions to perform SQL operations in the database.
These methods provide a way to build Relational Database Management System (RDBMS) functions to perform SQL operations on record data. You can use these methods in both scoped and global server scripts.
- Construct a function using the GlideDBFunctionBuilder constructor and associated methods.
- After building a function, you apply the function to the current record using the addFunction() method of the GlideRecord class.
- Add the function to a query using the addQuery() method of the GlideRecord class.
- Retrieve the results of the function using the existing GlideRecord API methods such as getValue() and getElement().
For example:
For additional information on Function fields, see Function field.
Scoped GlideDBFunctionBuilder - GlideDBFunctionBuilder()
Instantiates a GlideDBFunctionBuilder object.
Name | Type | Description |
---|---|---|
None |
Example
Scoped GlideDBFunctionBuilder - andFunc(expr1, expr2, …)
Begins a new AND expression, which returns true only if all expressions provided to it as parameters are true. At least one boolean-valued expression must be provided as a parameter.
Name | Type | Description |
---|---|---|
expression | Boolean-valued GlideFunction Expression | At present, glide functions that meet this criteria include and(), or(), and compare(). |
Type | Description |
---|---|
String | The resultant GlideFunction expression. |
Example
The following example shows how to define and build a simple case statement.
Output:
Scoped GlideDBFunctionBuilder - add()
Adds the values of two or more integer fields.
Use the field(String field) method to define fields on which the operation is performed.
Name | Type | Description |
---|---|---|
None |
Type | Description |
---|---|
void |
Example
Scoped GlideDBFunctionBuilder - build()
Builds the database function defined by the GlideDBFunctionBuilder object.
Name | Type | Description |
---|---|---|
None |
Type | Description |
---|---|
void |
Example
Scoped GlideDBFunctionBuilder - coalesce()
Takes any number of comma-separated fields as input and returns the first non-empty value.
Name | Type | Description |
---|---|---|
argument | String | A list containing a constant value (strings, objects, booleans) or a field. Accepted format: glidefunction:coalesce(<string, string, string, etc.>) . An argument can have any number of parameters but
must have at least one parameter defined. |
Type | Description |
---|---|
String | The first value in the argument list that is non-null. If no values are non-null, null is returned. If no arguments are provided in the creation of the function object, "invalid function" is returned as a string. |
Example
The following example request demonstrates how to form the coalesce guide function to return null values according to closed_at, resolved_at, and sys_updated_on fields.
The script returns the first non-null value from a field out of this list: [closed_at, resolved_at, sys_updated_on] for each incident record in the incident table and outputs them with gs.info
.
Scoped GlideDBFunctionBuilder - compare(exp1, op, exp2)
Begins a new COMPARE expression which performs a comparison between two expressions.
Name | Type | Description |
---|---|---|
exp1 | String | Left comparison expression. Can be a constant, reference to a field, or another Glide function. |
op | String | A comparison operator, surrounded with quotes. Accepted values:
|
exp2 | String | Right comparison expression. Can be a constant, reference to a field, or another Glide function. |
Type | Description |
---|---|
String | The resultant GlideFunction expression. |
Example
The following example shows how to build a glidefunction expression using the compare() operation. After calling compare(), three additional calls must be made to specify each of the three required parameters, which is then completed by a single call to endfun(). Below, we create two separate compare() expressions and provide them as parameters to the or() expression.
Output:
Scoped GlideDBFunctionBuilder - concat()
Concatenates the values of two or more fields.
Use the field(String field) method to define fields on which the operation is performed.
Name | Type | Description |
---|---|---|
None |
Type | Description |
---|---|
void |
Example
Scoped GlideDBFunctionBuilder - constant(String constant)
Defines a constant value to use in the function. If used with the dayofweek() method, the string defines whether to use Sunday or Monday as the first day of the week.
Name | Type | Description |
---|---|---|
constant | String | A constant value used in a function. When used with the dayofweek() method, the value defines whether the week starts on a Sunday or Monday.
This definition enables the dayofweek() method to return the correct day of the week from a given date. If a value other than 1 or 2 is provided, the dayofweek() method uses Sunday as the first day of the week. |
Type | Description |
---|---|
void |
Example
The following code example shows how to call this method.
Scoped GlideDBFunctionBuilder - datediff()
Determines the duration using a given start date/time and end date/time.
Use the field(String field) method to define start and end date/time fields.
Name | Type | Description |
---|---|---|
None |
Type | Description |
---|---|
void |
Example
Scoped GlideDBFunctionBuilder - dayofweek()
Returns an integer representing the day of the week for a given date.
Use the field(String field) method to define the given date/time. Use the constant(String constant) method to define whether the week starts on a Sunday or Monday.
This method can be used with MySQL, Oracle, and Microsoft SQL Server databases only. If using an Oracle database, the NLS_TERRITORY setting must be set to a territory with Sunday as the first day of the week.
Name | Type | Description |
---|---|---|
None |
Type | Description |
---|---|
Integer | If the first day of the week is set to Sunday in the constant(String constant) method, return values are associated with the following days of the week:
If the first day of the week is set to Monday:
If a value other than 1 or 2 is provided in the constant(String constant) method, the dayofweek() method uses Sunday as the first day of the week. |
Example
Scoped GlideDBFunctionBuilder - divide()
Divides the value of one integer field by another.
Use the field(String field) method to define fields on which the operation is performed.
Name | Type | Description |
---|---|---|
None |
Type | Description |
---|---|
void |
Example
Scoped GlideDBFunctionBuilder - field(String field)
Defines a field on which a SQL operation is performed.
Name | Type | Description |
---|---|---|
field | String | The field on which you are performing the SQL operation. |
Type | Description |
---|---|
void |
Example
Scoped GlideDBFunctionBuilder - length()
Determines the number of code units in a field.
Use the field(String field) method to define fields on which the operation is performed.
Name | Type | Description |
---|---|---|
None |
Type | Description |
---|---|
void |
Example
Scoped GlideDBFunctionBuilder - multiply()
Multiplies the values of two integer fields.
Use the field(String field) method to define fields on which the operation is performed.
Name | Type | Description |
---|---|---|
None |
Type | Description |
---|---|
void |
Example
Scoped GlideDBFunctionBuilder - orFunc(expression)
Begins a new OR expression which returns true if at least one of the expressions provided to it as a parameter is true.
At least one boolean-valued expression must be provided as a parameter.
Name | Type | Description |
---|---|---|
expression | Boolean-valued GlideFunction Expression | At Present, glide functions that meet this criteria include and(), or(), and compare(). |
Type | Description |
---|---|
String | The resultant GlideFunction expression. |
Example
The following example shows how to build a glidefunction expression using the or() operation. The expression returns true if either the first_name field is 'John" or the active field is true. The resulting expression string may then be used wherever glidefunction expressions are accepted.
Output:
Scoped GlideDBFunctionBuilder - position()
Returns the first occurrence of a specified string within a column of a table.
Optionally you can specify a location within the string to start the search. This method equates to the LOCATE(substring, str, position)
in MySQL.
You can use this method to find interesting data points or custom reports by data analysts. Typically you will use this method for data warehousing or to extract, transform and load (ETL) data into less flexible systems.
To use this method in Dictionary records, use glidefunction:position(<serarch_term>,<column>)
.
Name | Type | Description |
---|---|---|
search_term | String | Text to search for in the specified table column. |
column | String | Name of the table column to search. The table is specified in the associated GlideRecord object. |
start_position | Number | Optional. Location in the column text to start the search. Default: 1 |
Type | Description |
---|---|
None | Position of the first occurrence of the specified search term. Returns 0 if the search term is not found in the associated text. Returns NULL if any required argument is NULL. |
Example
The following code example shows how to call this method.
Output:
Scoped GlideDBFunctionBuilder - substring()
Returns a substring from a specified column of a table starting at a specified location.
Optionally you can also specify a location within the string where to stop the text extraction. This method equates to the LOCATE(substring, str, position)
in MySQL.
This method works similar to that of JavaScript, just at the database level. You can use this method to find interesting data points or custom reports for data analysts. Typically you will use this method for data warehousing or to extract, transform and load (ETL) data into less flexible systems.
To use this method in Dictionary records, use glidefunction:substring(<field>,<start_position>,<end_position>)
Name | Type | Description |
---|---|---|
field | String | Name of the column in the table from which to obtain the text. The table is specified in the associated GlideRecord object. |
start_position | Number | Location in the column text to start extracting text. |
end_position | Number | Optional. Location in the column text to stop extracting text. Default: End of text in the specified column. |
Type | Description |
---|---|
String | Text extracted from the specified table column. |
Example
The following code example shows how to call this method.
Output:
Scoped GlideDBFunctionBuilder - subtract()
Subtracts the value of one integer field from another.
Use the field(String field) method to define fields on which the operation is performed.
Name | Type | Description |
---|---|---|
None |
Type | Description |
---|---|
void |
Example
On this page
- Scoped GlideDBFunctionBuilder - GlideDBFunctionBuilder()
- Scoped GlideDBFunctionBuilder - andFunc(expr1, expr2, …)
- Scoped GlideDBFunctionBuilder - add()
- Scoped GlideDBFunctionBuilder - build()
- Scoped GlideDBFunctionBuilder - coalesce()
- Scoped GlideDBFunctionBuilder - compare(exp1, op, exp2)
- Scoped GlideDBFunctionBuilder - concat()
- Scoped GlideDBFunctionBuilder - constant(String constant)
- Scoped GlideDBFunctionBuilder - datediff()
- Scoped GlideDBFunctionBuilder - dayofweek()
- Scoped GlideDBFunctionBuilder - divide()
- Scoped GlideDBFunctionBuilder - field(String field)
- Scoped GlideDBFunctionBuilder - length()
- Scoped GlideDBFunctionBuilder - multiply()
- Scoped GlideDBFunctionBuilder - orFunc(expression)
- Scoped GlideDBFunctionBuilder - position()
- Scoped GlideDBFunctionBuilder - substring()
- Scoped GlideDBFunctionBuilder - subtract()