{"revision":"c10276e953456835cdbb1f3a60d58ae1980be353","document":{"id":785,"path":"api-reference/server-api-reference/GlideDBFunctionCaseBuilderAPI.md","title":"GlideDBFunctionCaseBuilder - Global","bundle":null,"doc_type":null,"release":"australia","body":"\n# GlideDBFunctionCaseBuilder- Global\n\nThe GlideDBCaseStatementBuilder API allows you to build SQL CASE statements and return values based on a series of conditions.\n\nYou can use this API as part of the definition of the [GlideElement - Global](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideElementAPI.md) API or when querying a table. To build a `CASE` statement, start by instantiating the GlideDBFunctionCaseBuilder object using its constructor, GlideDBFunctionCaseBuilder\\(\\). Then, define one or more cases using a series of when\\(\\) and then\\(\\) calls followed by a single, optional otherwise\\(\\) call. Lastly, call the build\\(\\) method to finalize the builder and produce the completed database function case statement. This statement can then be used directly or as part of other Glide function definitions.\n\nRequired roles: Admin, function\\_field\\_admin.\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## GlideDBFunctionCaseBuilder - GlideDBFunctionCaseBuilder\\(\\)\n\nInstantiates a GlideDBFunctionCaseBuilder object.\n\n|Name|Type|Description|\n|----|----|-----------|\n|None| ||\n\nThis example instantiates a GlideDBFunctionCaseBuilder object.\n\n```\nvar caseBuilder = new GlideDBFunctionCaseBuilder();\n\n```\n\n## GlideDBFunctionCaseBuilder - build\\(\\)\n\nConcludes the list of conditions and builds the final CASE statement.\n\n|Name|Type|Description|\n|----|----|-----------|\n|None| | |\n\n|Type|Description|\n|----|-----------|\n|string|The completed case statement.|\n\nThe following example shows how to define and build a simple case statement. Depending on the numerical value of the impact field, it will output one of four different strings.\n\n```\nconst caseStatement = new GlideDBFunctionCaseBuilder()\n    .whenCompare(\"impact\", \"'='\", \"'1'\").then(\"'CRITICAL'\")\n    .whenCompare(\"impact\", \"'='\", \"'2'\").then(\"'URGENT'\")\n    .whenCompare(\"impact\", \"'='\", \"'3'\").then(\"'IMPORTANT'\")\n    .otherwise(\"'NOT IMPORTANT'\")\n    .build();\n\nconst gr_incident = new GlideRecord(\"incident\");\ngr_incident.addFunction(caseStatement);\ngr_incident.setLimit(10);\ngr_incident.query();\nwhile(gr_incident.next())  {\n    gs.info(\"impact: {0}  case: {1}\",\n            [gr_incident.impact, gr_incident.getValue(caseStatement)]);\n}\n```\n\nOutput:\n\n```\n*** Script: impact: 2  case: URGENT\n*** Script: impact: 2  case: URGENT\n*** Script: impact: 1  case: CRITICAL\n*** Script: impact: 2  case: URGENT\n*** Script: impact: 2  case: URGENT\n*** Script: impact: 3  case: IMPORTANT\n*** Script: impact: 1  case: CRITICAL\n*** Script: impact: 1  case: CRITICAL\n*** Script: impact: 3  case: IMPORTANT\n*** Script: impact: 3  case: IMPORTANT\n```\n\n## GlideDBFunctionCaseBuilder - otherwise\\(String exp\\)\n\nOptional. Following at least one pair of calls to when and then, the otherwise\\(\\) call allows you to specify a value to output if none of the when expressions are satisfied.\n\n|Name|Type|Description|\n|----|----|-----------|\n|exp|String|Value to output. Can be a constant, a reference to a field, or another Glide function.|\n\n|Type|Description|\n|----|-----------|\n|GlideDBFunctionCaseBuilder|The fluent builder object to continue building the statement.|\n\nThe following example shows how to define and build a simple case statement.\n\n```\nconst caseStatement = new GlideDBCaseFunctionBuilder()\n    .whenEqual(\"impact\", \"'1'\").then(\"'CRITICAL'\") \n    .whenEqual(\"impact\", \"'2'\").then(\"category\")\n    .whenEqual(\"impact\", \"'3'\").then(\"length(short_description)\")\n    .otherwise(\"'NOT IMPORTANT'\")\n    .build();\n\nconst gr_incident = new GlideRecord(\"incident\");\ngr_incident.addFunction(caseStatement);\ngr_incident.setLimit(10);\ngr_incident.query();\nwhile(gr_incident.next())  {\n      gs.info(\"impact: {0}  case: {1}\",\n              [gr_incident.impact, gr_incident.getValue(caseStatement)]);\n}\n```\n\nOutput:\n\n```\n*** Script: impact: 2  case: inquiry\n*** Script: impact: 2  case: Hardware\n*** Script: impact: 1  case:  CRITICAL\n*** Script: impact: 2  case: database\n*** Script: impact: 2  case: inquiry\n*** Script: impact: 3  case: 28\n*** Script: impact: 1  case: CRITICAL\n*** Script: impact: 1  case: CRITICAL\n*** Script: impact: 3  case: 17\n*** Script: impact: 3  case: 29\n```\n\n## GlideDBFunctionCaseBuilder - then\\(String exp\\)\n\nFollowing any when\\(\\) method, then\\(\\) allows you to specify a value to output if the when\\(\\) expression is satisfied.\n\n|Name|Type|Description|\n|----|----|-----------|\n|exp|String|Value to output. Can be a constant, reference to a field, or another Glide function.|\n\n|Type|Description|\n|----|-----------|\n|GlideDBFunctionCaseBuilder|The fluent builder object to continue building the statement.|\n\nThe following example shows how to define and build a simple case statement. In this case, the then\\(\\) expressions show how you can output not only strings, but also the results of other glide functions such as the `length(short_description)`.\n\n```\nconst caseStatement = new GlideDBCaseFunctionBuilder()\n    .whenEqual(\"impact\", \"'1'\").then(\"'CRITICAL'\") \n    .whenEqual(\"impact\", \"'2'\").then(\"category\")\n    .whenEqual(\"impact\", \"'3'\").then(\"length(short_description)\")\n    .otherwise(\"'NOT IMPORTANT'\")\n    .build();\n\nconst gr_incident = new GlideRecord(\"incident\");\ngr_incident.addFunction(caseStatement);\ngr_incident.setLimit(10);\ngr_incident.query();\nwhile(gr_incident.next())  {\n      gs.info(\"impact: {0}  case: {1}\",\n              [gr_incident.impact, gr_incident.getValue(caseStatement)]);\n}\n```\n\nOutput:\n\n```\n*** Script: impact: 2  case: inquiry\n*** Script: impact: 2  case: Hardware\n*** Script: impact: 1  case:  CRITICAL\n*** Script: impact: 2  case: database\n*** Script: impact: 2  case: inquiry\n*** Script: impact: 3  case: 28\n*** Script: impact: 1  case: CRITICAL\n*** Script: impact: 1  case: CRITICAL\n*** Script: impact: 3  case: 17\n*** Script: impact: 3  case: 29\n```\n\n## GlideDBFunctionCaseBuilder - when\\(String func\\)\n\nThe most versatile of the supported 'when' expressions, the when\\(\\) method allows you to supply any boolean-valued Glide function as the condition to satisfy.\n\n<table id=\"table_dmg_sy2_xwb\" class=\"parameters\"><thead><tr><th>\n\nName\n\n</th><th>\n\nType\n\n</th><th>\n\nDescription\n\n</th></tr></thead><tbody><tr><td>\n\nfunc\n\n</td><td>\n\nString\n\n</td><td>\n\nA Glide function that results in a boolean-valued result when evaluated. Possible values: -   glidefunction:compare\n-   glidefunction:and\n-   glidefunction:or\n\n</td></tr></tbody>\n</table>|Type|Description|\n|----|-----------|\n|glideDBFunctionCaseBuilder|The object to continue building the statement.|\n\nThe following example shows how to define and build a simple case statement. In this case, the when\\(\\) method is used to specify more complex comparison expressions. If the expression evaluates to true, the corresponding string is returned.\n\n```\nconst caseStatement = new GlideDBFunctionCaseBuilder() \n    // if impact == 1 AND category == 'Software' then output \"CRITICAL SOFTWARE\" \n    .when(\"and(compare(impact, '=', '1'), compare(category, '=',  \n'Software')\") \n        .then(\"'CRITICAL SOFTWARE'\") \n    // if impact == 1 then output \"CRITICAL OTHER\" \n    .when(\"compare(impact, '=', '1')\") \n        .then(\"'CRITICAL OTHER'\") \n    // if impact == 2 OR category == 'Software' then output \"URGENT\" \n    .when(\"or(compare(impact, '=', '2'), compare(category, '=', 'Software')\") \n        .then(\"'URGENT'\") \n    // If none of the above expressions are true, output \"NOT IMPORTANT\" \n    .otherwise(\"'NOT IMPORTANT'\") \n    .build(); \n \nconst gr_incident = new GlideRecord(\"incident\");\ngr_incident.addFunction(caseStatement); \ngr_incident.setLimit(20); \ngr_incident.query(); \nwhile(gr_incident.next())  { \n      gs.info(\"impact: {0}  category: {1}  case: {2}\", \n            [gr_incident.impact, gr_incident.category, gr_incident.getValue(caseStatement)]); \n}\n```\n\nOutput:\n\n```\nimpact: 2  category: inquiry  case: URGENT\nimpact: 2  category: Hardware  case: URGENT\nimpact: 1  category: inquiry  case:  CRITICAL OTHER\nimpact: 2  category: database  case: URGENT\nimpact: 2  category: inquiry  case: URGENT\nimpact: 3  category: software  case: URGENT\nimpact: 1  category: inquiry  case: CRITICAL OTHER\nimpact: 1  category: inquiry  case: CRITICAL OTHER\nimpact: 3  category: inquiry  case: NOT IMPORTANT\nimpact: 3  category: inquiry  case: NOT IMPORTANT\nimpact: 3  category: hardware  case: NOT IMPORTANT\nimpact: 3  category: inquiry  case: NOT IMPORTANT\nimpact: 3  category: hardware  case: NOT IMPORTANT\nimpact: 3  category: inquiry  case: NOT IMPORTANT\nimpact: 1  category: inquiry  case: CRITICAL OTHER\nimpact: 1  category: software  case: CRITICAL SOFTWARE\nimpact: 3  category: inquiry  case: NOT IMPORTANT\nimpact: 3  category: inquiry  case: NOT IMPORTANT\nimpact: 2  category: software  case: URGENT\nimpact: 1  category: software  case: CRITICAL SOFTWARE\n```\n\n## GlideDBFunctionCaseBuilder - whenCompare\\(String exp1, String op, String exp2\\)\n\nBegins a comparison case condition.\n\n<table id=\"table_jj1_2q2_xwb\" class=\"parameters\"><thead><tr><th>\n\nName\n\n</th><th>\n\nType\n\n</th><th>\n\nDescription\n\n</th></tr></thead><tbody><tr><td>\n\nexp1\n\n</td><td>\n\nString\n\n</td><td>\n\nLeft comparison expression. Can be a constant, reference to a field, or another Glide function.\n\n</td></tr><tr><td>\n\nop\n\n</td><td>\n\nString\n\n</td><td>\n\nComparison operator, surrounded with quotes. Valid values:\n\n-   \"'='\"\n-   \"'&lt;'\"\n-   \"'&gt;'\"\n-   \"'&lt;='\"\n-   \"'&gt;='\"\n\n</td></tr><tr><td>\n\nexp2\n\n</td><td>\n\nString\n\n</td><td>\n\nRight comparison expression. Can be a constant, reference to a field, or another Glide function.\n\n</td></tr></tbody>\n</table>|Type|Description|\n|----|-----------|\n|GlideDBFunctionCaseBuilder|The function builder object to continue building the statement.|\n\nThe following example shows how to define and build a simple case statement. In this case, the whenCompare\\(\\) method is used to compare the value of the impact field to a number and output a specified string if they match.\n\n```\nconst caseStatement = new GlideDBFunctionCaseBuilder()\n    .whenCompare(\"impact\", \"'='\", \"'1'\").then(\"'CRITICAL'\")\n    .whenCompare(\"impact\", \"'='\", \"'2'\").then(\"URGENT\")\n    .whenCompare(\"impact\", \"'='\", \"'3'\").then(\"IMPORTANT\")\n    .otherwise(\"'NOT IMPORTANT'\")\n    .build();\n\nconst gr_incident = new GlideRecord(\"incident\");\ngr_incident.addFunction(caseStatement);\ngr_incident.setLimit(10);\ngr_incident.query();\nwhile(gr_incident.next()) {\n    gs.info(\"impact: {0} case: {1}\",\n            [gr_incident.impact, gr_incident.getValue(caseStatement)]);\n}\n```\n\nOutput:\n\n```\n*** Script: impact: 2  case: URGENT\n*** Script: impact: 2  case: URGENT\n*** Script: impact: 1  case: CRITICAL\n*** Script: impact: 2  case: URGENT\n*** Script: impact: 2  case: URGENT\n*** Script: impact: 3  case: IMPORTANT\n*** Script: impact: 1  case: CRITICAL\n*** Script: impact: 1  case: CRITICAL\n*** Script: impact: 3  case: IMPORTANT\n*** Script: impact: 3  case: IMPORTANT\n\n```\n\n## GlideDBFunctionCaseBuilder - whenEqual\\(String exp1, String exp2\\)\n\nSimilar to whenCompare\\(\\), whenEqual\\(\\) is a convenient method for checking equality. It is equivalent to whenCompare\\(leftExpression, ‘=’, rightExpression\\).\n\n|Name|Type|Description|\n|----|----|-----------|\n|exp1|String|Left comparison expression. Can be a constant, a reference to a field, or another Glide function.|\n|exp2|String|Right comparison expression. Can be a constant, a reference to a field, or another Glide function.|\n\n|Type|Description|\n|----|-----------|\n|GlideDBFunctionCaseBuilder|The function builder object to continue building the statement.|\n\nThe following example shows how to define and build a simple case statement. In this case, the whenEqual\\(\\) method is used to compare the value of the impact field to a number and output a specified string if they match.\n\n```\nconst caseStatement = new GlideDBFunctionCaseBuilder()\n    .whenEqual(\"impact\", \"'1'\").then(\"'CRITICAL'\")\n    .whenEqual(\"impact\", \"'2'\").then(\"'URGENT'\")\n    .whenEqual(\"impact\", \"'3'\").then(\"'IMPORTANT'\")\n    .otherwise(\"'NOT IMPORTANT'\")\n    .build();\n\nconst gr_incident = new GlideRecord(\"incident\");\ngr_incident.addFunction(caseStatement);\ngr_incident.setLimit(10);\ngr_incident.query();\nwhile(gr_incident.next())  {\n    gs.info(\"impact: {0}  case: {1}\",\n            [gr_incident.impact, gr_incident.getValue(caseStatement)]);\n}\n```\n\nOutput:\n\n```\n*** Script: impact: 2  case: URGENT\n*** Script: impact: 2  case: URGENT\n*** Script: impact: 1  case: CRITICAL\n*** Script: impact: 2  case: URGENT\n*** Script: impact: 2  case: URGENT\n*** Script: impact: 3  case: IMPORTANT\n*** Script: impact: 1  case: CRITICAL\n*** Script: impact: 1  case: CRITICAL\n*** Script: impact: 3  case: IMPORTANT\n*** Script: impact: 3  case: IMPORTANT\n```\n\n"}}