GlideDBFunctionCaseBuilder - Global

The GlideDBCaseStatementBuilder API allows you to build SQL CASE statements and return values based on a series of conditions.

You can use this API as part of the definition of the GlideElement - Global 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.

Required roles: Admin, function_field_admin.

GlideDBFunctionCaseBuilder - GlideDBFunctionCaseBuilder()

Instantiates a GlideDBFunctionCaseBuilder object.

Table 1. Parameters

Name	Type	Description
None

This example instantiates a GlideDBFunctionCaseBuilder object.

var caseBuilder = new GlideDBFunctionCaseBuilder();

GlideDBFunctionCaseBuilder - build()

Concludes the list of conditions and builds the final CASE statement.

Table 2. Parameters

Name	Type	Description
None

Table 3. Returns

Type	Description
string	The completed case statement.

The 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.

const caseStatement = new GlideDBFunctionCaseBuilder()
    .whenCompare("impact", "'='", "'1'").then("'CRITICAL'")
    .whenCompare("impact", "'='", "'2'").then("'URGENT'")
    .whenCompare("impact", "'='", "'3'").then("'IMPORTANT'")
    .otherwise("'NOT IMPORTANT'")
    .build();

const gr_incident = new GlideRecord("incident");
gr_incident.addFunction(caseStatement);
gr_incident.setLimit(10);
gr_incident.query();
while(gr_incident.next())  {
    gs.info("impact: {0}  case: {1}",
            [gr_incident.impact, gr_incident.getValue(caseStatement)]);
}

Output:

*** Script: impact: 2  case: URGENT
*** Script: impact: 2  case: URGENT
*** Script: impact: 1  case: CRITICAL
*** Script: impact: 2  case: URGENT
*** Script: impact: 2  case: URGENT
*** Script: impact: 3  case: IMPORTANT
*** Script: impact: 1  case: CRITICAL
*** Script: impact: 1  case: CRITICAL
*** Script: impact: 3  case: IMPORTANT
*** Script: impact: 3  case: IMPORTANT

GlideDBFunctionCaseBuilder - otherwise(String exp)

Optional. 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.

Table 4. Parameters

Name	Type	Description
exp	String	Value to output. Can be a constant, a reference to a field, or another Glide function.

Table 5. Returns

Type	Description
GlideDBFunctionCaseBuilder	The fluent builder object to continue building the statement.

The following example shows how to define and build a simple case statement.

const caseStatement = new GlideDBCaseFunctionBuilder()
    .whenEqual("impact", "'1'").then("'CRITICAL'") 
    .whenEqual("impact", "'2'").then("category")
    .whenEqual("impact", "'3'").then("length(short_description)")
    .otherwise("'NOT IMPORTANT'")
    .build();

const gr_incident = new GlideRecord("incident");
gr_incident.addFunction(caseStatement);
gr_incident.setLimit(10);
gr_incident.query();
while(gr_incident.next())  {
      gs.info("impact: {0}  case: {1}",
              [gr_incident.impact, gr_incident.getValue(caseStatement)]);
}

Output:

*** Script: impact: 2  case: inquiry
*** Script: impact: 2  case: Hardware
*** Script: impact: 1  case:  CRITICAL
*** Script: impact: 2  case: database
*** Script: impact: 2  case: inquiry
*** Script: impact: 3  case: 28
*** Script: impact: 1  case: CRITICAL
*** Script: impact: 1  case: CRITICAL
*** Script: impact: 3  case: 17
*** Script: impact: 3  case: 29

GlideDBFunctionCaseBuilder - then(String exp)

Following any when() method, then() allows you to specify a value to output if the when() expression is satisfied.

Table 6. Parameters

Name	Type	Description
exp	String	Value to output. Can be a constant, reference to a field, or another Glide function.

Table 7. Returns

Type	Description
GlideDBFunctionCaseBuilder	The fluent builder object to continue building the statement.

The 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).

const caseStatement = new GlideDBCaseFunctionBuilder()
    .whenEqual("impact", "'1'").then("'CRITICAL'") 
    .whenEqual("impact", "'2'").then("category")
    .whenEqual("impact", "'3'").then("length(short_description)")
    .otherwise("'NOT IMPORTANT'")
    .build();

const gr_incident = new GlideRecord("incident");
gr_incident.addFunction(caseStatement);
gr_incident.setLimit(10);
gr_incident.query();
while(gr_incident.next())  {
      gs.info("impact: {0}  case: {1}",
              [gr_incident.impact, gr_incident.getValue(caseStatement)]);
}

Output:

*** Script: impact: 2  case: inquiry
*** Script: impact: 2  case: Hardware
*** Script: impact: 1  case:  CRITICAL
*** Script: impact: 2  case: database
*** Script: impact: 2  case: inquiry
*** Script: impact: 3  case: 28
*** Script: impact: 1  case: CRITICAL
*** Script: impact: 1  case: CRITICAL
*** Script: impact: 3  case: 17
*** Script: impact: 3  case: 29

GlideDBFunctionCaseBuilder - when(String func)

The most versatile of the supported 'when' expressions, the when() method allows you to supply any boolean-valued Glide function as the condition to satisfy.

Table 8. Parameters

Name	Type	Description
func	String	A Glide function that results in a boolean-valued result when evaluated. Possible values: glidefunction:compare glidefunction:and glidefunction:or

Table 9. Returns

Type	Description
glideDBFunctionCaseBuilder	The object to continue building the statement.

The 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.

const caseStatement = new GlideDBFunctionCaseBuilder() 
    // if impact == 1 AND category == 'Software' then output "CRITICAL SOFTWARE" 
    .when("and(compare(impact, '=', '1'), compare(category, '=',  
'Software')") 
        .then("'CRITICAL SOFTWARE'") 
    // if impact == 1 then output "CRITICAL OTHER" 
    .when("compare(impact, '=', '1')") 
        .then("'CRITICAL OTHER'") 
    // if impact == 2 OR category == 'Software' then output "URGENT" 
    .when("or(compare(impact, '=', '2'), compare(category, '=', 'Software')") 
        .then("'URGENT'") 
    // If none of the above expressions are true, output "NOT IMPORTANT" 
    .otherwise("'NOT IMPORTANT'") 
    .build(); 
 
const gr_incident = new GlideRecord("incident");
gr_incident.addFunction(caseStatement); 
gr_incident.setLimit(20); 
gr_incident.query(); 
while(gr_incident.next())  { 
      gs.info("impact: {0}  category: {1}  case: {2}", 
            [gr_incident.impact, gr_incident.category, gr_incident.getValue(caseStatement)]); 
}

Output:

impact: 2  category: inquiry  case: URGENT
impact: 2  category: Hardware  case: URGENT
impact: 1  category: inquiry  case:  CRITICAL OTHER
impact: 2  category: database  case: URGENT
impact: 2  category: inquiry  case: URGENT
impact: 3  category: software  case: URGENT
impact: 1  category: inquiry  case: CRITICAL OTHER
impact: 1  category: inquiry  case: CRITICAL OTHER
impact: 3  category: inquiry  case: NOT IMPORTANT
impact: 3  category: inquiry  case: NOT IMPORTANT
impact: 3  category: hardware  case: NOT IMPORTANT
impact: 3  category: inquiry  case: NOT IMPORTANT
impact: 3  category: hardware  case: NOT IMPORTANT
impact: 3  category: inquiry  case: NOT IMPORTANT
impact: 1  category: inquiry  case: CRITICAL OTHER
impact: 1  category: software  case: CRITICAL SOFTWARE
impact: 3  category: inquiry  case: NOT IMPORTANT
impact: 3  category: inquiry  case: NOT IMPORTANT
impact: 2  category: software  case: URGENT
impact: 1  category: software  case: CRITICAL SOFTWARE

GlideDBFunctionCaseBuilder - whenCompare(String exp1, String op, String exp2)

Begins a comparison case condition.

Table 10. Parameters

Name	Type	Description
exp1	String	Left comparison expression. Can be a constant, reference to a field, or another Glide function.
op	String	Comparison operator, surrounded with quotes. Valid values: "'='" "'<'" "'>'" "'<='" "'>='"
exp2	String	Right comparison expression. Can be a constant, reference to a field, or another Glide function.

Table 11. Returns

Type	Description
GlideDBFunctionCaseBuilder	The function builder object to continue building the statement.

The 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.

const caseStatement = new GlideDBFunctionCaseBuilder()
    .whenCompare("impact", "'='", "'1'").then("'CRITICAL'")
    .whenCompare("impact", "'='", "'2'").then("URGENT")
    .whenCompare("impact", "'='", "'3'").then("IMPORTANT")
    .otherwise("'NOT IMPORTANT'")
    .build();

const gr_incident = new GlideRecord("incident");
gr_incident.addFunction(caseStatement);
gr_incident.setLimit(10);
gr_incident.query();
while(gr_incident.next()) {
    gs.info("impact: {0} case: {1}",
            [gr_incident.impact, gr_incident.getValue(caseStatement)]);
}

Output:

*** Script: impact: 2  case: URGENT
*** Script: impact: 2  case: URGENT
*** Script: impact: 1  case: CRITICAL
*** Script: impact: 2  case: URGENT
*** Script: impact: 2  case: URGENT
*** Script: impact: 3  case: IMPORTANT
*** Script: impact: 1  case: CRITICAL
*** Script: impact: 1  case: CRITICAL
*** Script: impact: 3  case: IMPORTANT
*** Script: impact: 3  case: IMPORTANT

GlideDBFunctionCaseBuilder - whenEqual(String exp1, String exp2)

Similar to whenCompare(), whenEqual() is a convenient method for checking equality. It is equivalent to whenCompare(leftExpression, ‘=’, rightExpression).

Table 12. Parameters

Name	Type	Description
exp1	String	Left comparison expression. Can be a constant, a reference to a field, or another Glide function.
exp2	String	Right comparison expression. Can be a constant, a reference to a field, or another Glide function.

Table 13. Returns

Type	Description
GlideDBFunctionCaseBuilder	The function builder object to continue building the statement.

The 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.

const caseStatement = new GlideDBFunctionCaseBuilder()
    .whenEqual("impact", "'1'").then("'CRITICAL'")
    .whenEqual("impact", "'2'").then("'URGENT'")
    .whenEqual("impact", "'3'").then("'IMPORTANT'")
    .otherwise("'NOT IMPORTANT'")
    .build();

const gr_incident = new GlideRecord("incident");
gr_incident.addFunction(caseStatement);
gr_incident.setLimit(10);
gr_incident.query();
while(gr_incident.next())  {
    gs.info("impact: {0}  case: {1}",
            [gr_incident.impact, gr_incident.getValue(caseStatement)]);
}

Output:

*** Script: impact: 2  case: URGENT
*** Script: impact: 2  case: URGENT
*** Script: impact: 1  case: CRITICAL
*** Script: impact: 2  case: URGENT
*** Script: impact: 2  case: URGENT
*** Script: impact: 3  case: IMPORTANT
*** Script: impact: 1  case: CRITICAL
*** Script: impact: 1  case: CRITICAL
*** Script: impact: 3  case: IMPORTANT
*** Script: impact: 3  case: IMPORTANT