{"revision":"c10276e953456835cdbb1f3a60d58ae1980be353","document":{"id":784,"path":"api-reference/server-api-reference/GlideDBFunctionBuilderScopedAPI.md","title":"GlideDBFunctionBuilder - Scoped, Global","bundle":null,"doc_type":null,"release":"australia","body":"\n# GlideDBFunctionBuilder - Scoped, Global\n\nThe GlideDBFunctionBuilder API provides methods to build functions to perform SQL operations in the database.\n\nThese 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.\n\nTo use platform functions:\n\n- Construct a function using the GlideDBFunctionBuilder constructor and associated methods.\n- After building a function, you apply the function to the current record using the addFunction\\(\\) method of the GlideRecord class.\n- Add the function to a query using the addQuery\\(\\) method of the GlideRecord class.\n- Retrieve the results of the function using the existing GlideRecord API methods such as getValue\\(\\) and getElement\\(\\).\n\nFor example:\n\n```\nvar functionBuilder = new GlideDBFunctionBuilder();\nvar dbFunction = functionBuilder.position();\ndbFunction = functionBuilder.constant('my'); // search_term: Text to search for in the specified table column.\ndbFunction = functionBuilder.field('short_description'); // column: Name of the table column to search.\ndbFunction = functionBuilder.build();\n\ngs.log(dbFunction);\n\nvar gr_incident = new GlideRecord('incident'); // Table containing the column to search\ngr_incident.addFunction(dbFunction);\ngr_incident.addQuery(\"short_description\", \"CONTAINS\", \"my\");\ngr_incident.setLimit(20);\ngr_incident.query();\nwhile(gr_incident.next()) {\n gs.info(gr_incident.short_description + \"\\n position('my', short_description): \" + gr_incident.getValue(dbFunction));\n}\n```\n\nFor additional information on Function fields, see [Function field](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/platform-support-functions.md).\n\n**Parent Topic:**[Server API reference](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/api-server.md)\n\n## Scoped GlideDBFunctionBuilder - GlideDBFunctionBuilder\\(\\)\n\nInstantiates a GlideDBFunctionBuilder object.\n\n|Name|Type|Description|\n|----|----|-----------|\n|None| | |\n\n```\nvar builder = new GlideDBFunctionBuilder();\n\n```\n\n## Scoped GlideDBFunctionBuilder - andFunc\\(expr1, expr2, …\\)\n\nBegins 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.\n\n|Name|Type|Description|\n|----|----|-----------|\n|expression|Boolean-valued GlideFunction Expression|At present, glide functions that meet this criteria include and\\(\\), or\\(\\), and compare\\(\\).|\n\n|Type|Description|\n|----|-----------|\n|String|The resultant GlideFunction expression.|\n\nThe following example shows how to define and build a simple case statement.\n\n```\nvar expr = new GlideDBFunctionBuilder() \n .andFunc() \n .compare().field('first_name').constant('=').constant('John').endfunc() \n .compare().field('active').constant('=').constant(1).endfunc() \n .endfunc() \n .build(); \ngs.info(\"Expression: \" + expr);\n```\n\nOutput:\n\n```\nExpression: glidefunction:and(compare(first_name,'=','John'),compare(active,'=','1'))\n```\n\n## Scoped GlideDBFunctionBuilder - add\\(\\)\n\nAdds the values of two or more integer fields.\n\nUse the field\\(String field\\) method to define fields on which the operation is performed.\n\n|Name|Type|Description|\n|----|----|-----------|\n|None| | |\n\n|Type|Description|\n|----|-----------|\n|void| |\n\n```\nvar functionBuilder = new GlideDBFunctionBuilder();\nvar myAddingFunction = functionBuilder.add();\nmyAddingFunction = functionBuilder.field('order');\nmyAddingFunction = functionBuilder.field('priority');\nmyAddingFunction = functionBuilder.build();\n```\n\n## Scoped GlideDBFunctionBuilder - build\\(\\)\n\nBuilds the database function defined by the GlideDBFunctionBuilder object.\n\n|Name|Type|Description|\n|----|----|-----------|\n|None| | |\n\n|Type|Description|\n|----|-----------|\n|void| |\n\n```\nvar functionBuilder = new GlideDBFunctionBuilder();\nvar myAddingFunction = functionBuilder.add();\nmyAddingFunction = functionBuilder.field('order');\nmyAddingFunction = functionBuilder.field('priority');\nmyAddingFunction = functionBuilder.build();\ngs.info(myAddingFunction);\n```\n\nOutput:\n\n```\n*** Script: glidefunction:add(order,priority)\n```\n\n## Scoped GlideDBFunctionBuilder - coalesce\\(\\)\n\nTakes any number of comma-separated fields as input and returns the first non-empty value.\n\n|Name|Type|Description|\n|----|----|-----------|\n|argument|String|A list containing a constant value \\(strings, objects, booleans\\) or a field. Accepted format: `glidefunction:coalesce()`. An argument can have any number of parameters but must have at least one parameter defined.|\n\n|Type|Description|\n|----|-----------|\n|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.|\n\nThe 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.\n\n```\nvar gr = new GlideRecord('incident');\nvar func = \"glidefunction:coalesce(closed_at, resolved_at,sys_updated_on)\";\ngr.addFunction(func);\ngr.query(); \nwhile(gr.next())\ngs.info(gr.getValue(func));\n```\n\nThe 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`.\n\n```\n2016-12-14 02:46:44\n2018-01-09 22:55:16\n2018-01-07 22:54:55\n2018-01-13 23:02:54\n2018-01-09 23:12:02\n```\n\n## Scoped GlideDBFunctionBuilder - compare\\(exp1, op, exp2\\)\n\nBegins a new COMPARE expression which performs a comparison between two expressions.\n\n\n
\n\nName\n\n\n\nType\n\n\n\nDescription\n\n
\n\nexp1\n\n\n\nString\n\n\n\nLeft comparison expression. Can be a constant, reference to a field, or another Glide function.\n\n
\n\nop\n\n\n\nString\n\n\n\nA comparison operator, surrounded with quotes. Accepted values:- “’=’”\n- “’<’”\n- “’>’”\n- “’<=’”\n- “’>=’”\n\n
\n\nexp2\n\n\n\nString\n\n\n\nRight comparison expression. Can be a constant, reference to a field, or another Glide function.\n\n
|Type|Description|\n|----|-----------|\n|String|The resultant GlideFunction expression.|\n\nThe 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.\n\n```\nvar expr = new GlideDBFunctionBuilder() \n .orFunc() \n .compare().field('first_name').constant('=').constant('John').endfunc() \n .compare().field('active').constant('=').constant(1).endfunc() \n .endfunc() \n .build();\n```\n\nOutput:\n\n```\nExpression: glidefunction:or(compare(first_name,'=','John'),compare(active,'=','1'))\n```\n\n## Scoped GlideDBFunctionBuilder - concat\\(\\)\n\nConcatenates the values of two or more fields.\n\nUse the field\\(String field\\) method to define fields on which the operation is performed.\n\n|Name|Type|Description|\n|----|----|-----------|\n|None| | |\n\n|Type|Description|\n|----|-----------|\n|void| |\n\n```\nvar functionBuilder = new GlideDBFunctionBuilder();\nvar myConcatFunction = functionBuilder.concat();\nmyConcatFunction = functionBuilder.field('short_description');\nmyConcatFunction = functionBuilder.field('caller_id.name');\nmyConcatFunction = functionBuilder.build();\n```\n\n## Scoped GlideDBFunctionBuilder - constant\\(String constant\\)\n\nDefines 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.\n\n\n
\n\nName\n\n\n\nType\n\n\n\nDescription\n\n
\n\nconstant\n\n\n\nString\n\n\n\nA constant value used in a function. When used with the dayofweek\\(\\) method, the value defines whether the week starts on a Sunday or Monday.\n\n - **1**: Week begins on Sunday.\n- **2**: Week begins on Monday.\n\n 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.\n\n
|Type|Description|\n|----|-----------|\n|void| |\n\nThe following code example shows how to call this method.\n\n```\nvar functionBuilder = new GlideDBFunctionBuilder();\nvar dbFunction = functionBuilder.position();\ndbFunction = functionBuilder.constant('my');\ndbFunction = functionBuilder.field('short_description');\ndbFunction = functionBuilder.build();\n\ngs.log(dbFunction);\n\nvar g = new GlideRecord('incident');\ng.addFunction(dbFunction);\ng.addQuery(\"short_description\", \"CONTAINS\", \"my\");\ng.setLimit(20);\ng.query();\nwhile(g.next()) {\n gs.info(g.short_description + \"\\n position('my', short_description): \" + g.getValue(dbFunction));\n}\n```\n\n## Scoped GlideDBFunctionBuilder - datediff\\(\\)\n\nDetermines the duration using a given start date/time and end date/time.\n\nUse the field\\(String field\\) method to define start and end date/time fields.\n\n|Name|Type|Description|\n|----|----|-----------|\n|None| | |\n\n|Type|Description|\n|----|-----------|\n|void| |\n\n```\nvar functionBuilder = new GlideDBFunctionBuilder();\nvar myDateDiffFunction = functionBuilder.datediff();\nmyDateDiffFunction = functionBuilder.field('sys_updated_on');\nmyDateDiffFunction = functionBuilder.field('opened_at');\nmyDateDiffFunction = functionBuilder.build();\n```\n\n## Scoped GlideDBFunctionBuilder - dayofweek\\(\\)\n\nReturns an integer representing the day of the week for a given date.\n\nUse 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.\n\nThis 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.\n\n|Name|Type|Description|\n|----|----|-----------|\n|None| | |\n\n\n
\n\nType\n\n\n\nDescription\n\n
\n\nInteger\n\n\n\nIf 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:\n\n - **1**: Sunday\n- **2**: Monday\n- **3**: Tuesday\n- **4**: Wednesday\n- **5**: Thursday\n- **6**: Friday\n- **7**: Saturday\n\n If the first day of the week is set to Monday:\n\n - **1**: Monday\n- **2**: Tuesday\n- **3**: Wednesday\n- **4**: Thursday\n- **5**: Friday\n- **6**: Saturday\n- **7**: Sunday\n\n 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.\n\n
```\nvar functionBuilder = new GlideDBFunctionBuilder();\nvar dayOfWeekFunction = functionBuilder.dayofweek();\ndayOfWeekFunction = functionBuilder.field('opened_at');\ndayOfWeekFunction = functionBuilder.constant('2');\ndayOfWeekFunction = functionBuilder.build();\n\nvar now_GR = new GlideRecord('incident');\nnow_GR.addFunction(dayOfWeekFunction);\nnow_GR.query();\nwhile(now_GR.next())\ngs.log(now_GR.getValue(dayOfWeekFunction));\n\n```\n\n## Scoped GlideDBFunctionBuilder - divide\\(\\)\n\nDivides the value of one integer field by another.\n\nUse the field\\(String field\\) method to define fields on which the operation is performed.\n\n|Name|Type|Description|\n|----|----|-----------|\n|None| | |\n\n|Type|Description|\n|----|-----------|\n|void| |\n\n```\nvar functionBuilder = new GlideDBFunctionBuilder();\nvar myDivideFunction = functionBuilder.divide();\nmyDivideFunction = functionBuilder.field('order');\nmyDivideFunction = functionBuilder.field('priority');\nmyDivideFunction = functionBuilder.build();\n```\n\n## Scoped GlideDBFunctionBuilder - field\\(String field\\)\n\nDefines a field on which a SQL operation is performed.\n\n|Name|Type|Description|\n|----|----|-----------|\n|field|String|The field on which you are performing the SQL operation.|\n\n|Type|Description|\n|----|-----------|\n|void| |\n\n```\nvar functionBuilder = new GlideDBFunctionBuilder();\nvar myAddingFunction = functionBuilder.add();\nmyAddingFunction = functionBuilder.field('order');\nmyAddingFunction = functionBuilder.field('priority');\nmyAddingFunction = functionBuilder.build();\n```\n\n## Scoped GlideDBFunctionBuilder - length\\(\\)\n\nDetermines the number of code units in a field.\n\nUse the field\\(String field\\) method to define fields on which the operation is performed.\n\n|Name|Type|Description|\n|----|----|-----------|\n|None| | |\n\n|Type|Description|\n|----|-----------|\n|void| |\n\n```\nvar functionBuilder = new GlideDBFunctionBuilder();\nvar myLengthFunction = functionBuilder.length();\nmyLengthFunction = functionBuilder.field('short_description');\nmyLengthFunction = functionBuilder.build();\n\n```\n\n## Scoped GlideDBFunctionBuilder - multiply\\(\\)\n\nMultiplies the values of two integer fields.\n\nUse the field\\(String field\\) method to define fields on which the operation is performed.\n\n|Name|Type|Description|\n|----|----|-----------|\n|None| | |\n\n|Type|Description|\n|----|-----------|\n|void| |\n\n```\nvar functionBuilder = new GlideDBFunctionBuilder();\nvar myMultiplyFunction = functionBuilder.multiply();\nmyMultiplyFunction = functionBuilder.field('order');\nmyMultiplyFunction = functionBuilder.field('priority');\nmyMultiplyFunction = functionBuilder.build();\n```\n\n## Scoped GlideDBFunctionBuilder - orFunc\\(expression\\)\n\nBegins a new OR expression which returns true if at least one of the expressions provided to it as a parameter is true.\n\nAt least one boolean-valued expression must be provided as a parameter.\n\n|Name|Type|Description|\n|----|----|-----------|\n|expression|Boolean-valued GlideFunction Expression|At Present, glide functions that meet this criteria include and\\(\\), or\\(\\), and compare\\(\\).|\n\n|Type|Description|\n|----|-----------|\n|String|The resultant GlideFunction expression.|\n\nThe 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.\n\n```\nvar expr = new GlideDBFunctionBuilder() \n .orFunc() \n .compare().field('first_name').constant('=').constant('John').endfunc() \n .compare().field('active').constant('=').constant(1).endfunc() \n .endfunc() \n .build(); \ngs.info(\"Expression: \" + expr);\n```\n\nOutput:\n\n```\nExpression: glidefunction:or(compare(first_name,'=','John'),compare(active,'=','1'))\n```\n\n## Scoped GlideDBFunctionBuilder - position\\(\\)\n\nReturns the first occurrence of a specified string within a column of a table.\n\nOptionally you can specify a location within the string to start the search. This method equates to the `LOCATE(substring, str, position)` in MySQL.\n\nYou 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.\n\nTo use this method in Dictionary records, use `glidefunction:position(,)`.\n\n**Note:** The parameters specified in the following table are not passed with the method call, but rather are set in the GlideDBFunctionBuilder\\(\\) object.\n\n\n
\n\nName\n\n\n\nType\n\n\n\nDescription\n\n
\n\nsearch\\_term\n\n\n\nString\n\n\n\nText to search for in the specified table column.\n\n
\n\ncolumn\n\n\n\nString\n\n\n\nName of the table column to search. The table is specified in the associated GlideRecord object.\n\n
\n\nstart\\_position\n\n\n\nNumber\n\n\n\nOptional. Location in the column text to start the search.Default: 1\n\n
\n
\n\nType\n\n\n\nDescription\n\n
\n\nNone\n\n\n\nPosition 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.\n\n
The following code example shows how to call this method.\n\n```\nvar functionBuilder = new GlideDBFunctionBuilder();\nvar dbFunction = functionBuilder.position();\ndbFunction = functionBuilder.constant('my'); // search_term: Text to search for in the specified table column.\ndbFunction = functionBuilder.field('short_description'); // column: Name of the table column to search.\ndbFunction = functionBuilder.build();\n\ngs.log(dbFunction);\n\nvar gr_incident = new GlideRecord('incident'); // Table containing the column to search\ngr_incident.addFunction(dbFunction);\ngr_incident.addQuery(\"short_description\", \"CONTAINS\", \"my\");\ngr_incident.setLimit(20);\ngr_incident.query();\nwhile(gr_incident.next()) {\n gs.info(gr_incident.short_description + \"\\n position('my', short_description): \" + gr_incident.getValue(dbFunction));\n}\n```\n\nOutput:\n\n```\n*** Script: glidefunction:position('my',short_description)\n*** Script: Wireless access is down in my area\n position('my', short_description): 28\n*** Script: Printer in my office is out of toner\n position('my', short_description): 12\n*** Script: Reset my password\n position('my', short_description): 7\n*** Script: I can't launch my VPN client since the last software update\n position('my', short_description): 16\n*** Script: Missing my home directory\n position('my', short_description): 9\n*** Script: Seem to have an issue with my hard drive...\n position('my', short_description): 28\n*** Script: Please remove the latest hotfix from my PC\n position('my', short_description): 38\n*** Script: I can't get my weather report\n position('my', short_description): 13\n*** Script: Reset my password\n position('my', short_description): 7\n*** Script: my PDF docs are all locked from editing\n position('my', short_description): 1\n*** Script: My desk phone does not work\n position('my', short_description): 1\n*** Script: Can't log into SAP from my laptop today\n position('my', short_description): 25\n*** Script: My computer is not detecting the headphone device\n position('my', short_description): 1\n*** Script: My disk is still having issues. Can't delete a file\n position('my', short_description): 1\n*** Script: The USB port on my PC stopped working\n position('my', short_description): 17\n```\n\n## Scoped GlideDBFunctionBuilder - substring\\(\\)\n\nReturns a substring from a specified column of a table starting at a specified location.\n\nOptionally 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.\n\nThis 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.\n\nTo use this method in Dictionary records, use `glidefunction:substring(,,)`\n\n**Note:** The parameters specified in the following table are not passed with the method call, but rather are set in the GlideDBFunctionBuilder\\(\\) object.\n\n\n
\n\nName\n\n\n\nType\n\n\n\nDescription\n\n
\n\nfield\n\n\n\nString\n\n\n\nName of the column in the table from which to obtain the text. The table is specified in the associated GlideRecord object.\n\n
\n\nstart\\_position\n\n\n\nNumber\n\n\n\nLocation in the column text to start extracting text.\n\n
\n\nend\\_position\n\n\n\nNumber\n\n\n\nOptional. Location in the column text to stop extracting text.Default: End of text in the specified column.\n\n
|Type|Description|\n|----|-----------|\n|String|Text extracted from the specified table column.|\n\nThe following code example shows how to call this method.\n\n```\nvar functionBuilder = new GlideDBFunctionBuilder();\nvar dbFunction = functionBuilder.substring();\ndbFunction = functionBuilder.field('short_description'); // field: Column within the table to obtain the substring\ndbFunction = functionBuilder.constant(0); // start_position: Location in the column text to start extracting text\ndbFunction = functionBuilder.constant(20); // end_position: Location in the column text to stop extracting text.\ndbFunction = functionBuilder.build();\n\ngs.log(dbFunction);\n\nvar gr_incident = new GlideRecord('incident'); // Table containing the column from which to extract the text\ngr_incident.addFunction(dbFunction);\ngr_incident.addQuery(\"short_description\", \"CONTAINS\", \"my\");\ngr_incident.setLimit(20);\ngr_incident.query();\nwhile(gr_incident.next()) {\n gs.info(gr_incident.short_description + \"\\n substring(short_description, 0, 20): \" + gr_incident.getValue(dbFunction));\n}\n```\n\nOutput:\n\n```\n*** Script: glidefunction:substring(short_description,'0','20')\n*** Script: Wireless access is down in my area\n substring(short_description, 0, 20): Wireless access is \n*** Script: Printer in my office is out of toner\n substring(short_description, 0, 20): Printer in my offic\n*** Script: Reset my password\n substring(short_description, 0, 20): Reset my password\n*** Script: I can't launch my VPN client since the last software update\n substring(short_description, 0, 20): I can't launch my V\n*** Script: Missing my home directory\n substring(short_description, 0, 20): Missing my home dir\n*** Script: Seem to have an issue with my hard drive...\n substring(short_description, 0, 20): Seem to have an iss\n*** Script: Please remove the latest hotfix from my PC\n substring(short_description, 0, 20): Please remove the l\n*** Script: I can't get my weather report\n substring(short_description, 0, 20): I can't get my weat\n*** Script: Reset my password\n substring(short_description, 0, 20): Reset my password\n*** Script: my PDF docs are all locked from editing\n substring(short_description, 0, 20): my PDF docs are all\n*** Script: My desk phone does not work\n substring(short_description, 0, 20): My desk phone does \n*** Script: Can't log into SAP from my laptop today\n substring(short_description, 0, 20): Can't log into SAP \n*** Script: My computer is not detecting the headphone device\n substring(short_description, 0, 20): My computer is not \n*** Script: My disk is still having issues. Can't delete a file\n substring(short_description, 0, 20): My disk is still ha\n*** Script: The USB port on my PC stopped working\n substring(short_description, 0, 20): The USB port on my \n```\n\n## Scoped GlideDBFunctionBuilder - subtract\\(\\)\n\nSubtracts the value of one integer field from another.\n\nUse the field\\(String field\\) method to define fields on which the operation is performed.\n\n|Name|Type|Description|\n|----|----|-----------|\n|None| | |\n\n|Type|Description|\n|----|-----------|\n|void| |\n\n```\nvar functionBuilder = new GlideDBFunctionBuilder();\nvar mySubtractFunction = functionBuilder.subtract();\nmySubtractFunction = functionBuilder.field('order');\nmySubtractFunction = functionBuilder.field('priority');\nmySubtractFunction = functionBuilder.build();\n```\n\n"}}