Analytics SAQL Reference

SAQL Overview
SAQL Examples
SAQL Basic Elements
SAQL Operators
Arithmetic Operators
Comparison Operators
String Operators
Logical Operators
case
Null Operators
SAQL Statements
SAQL Functions
Query Performance
PDF

Newer Version Available

This content describes an older version of this product. View Latest

case

Use the SAQL case operator within a foreach statement to create logic that chooses between conditions. The case operator supports two syntax forms: searched case expression and simple case expression.

Syntax—Searched Case Expression

1case
2      when search_condition then result_expr    
3      [ when search_condition2 then result_expr2 … ]    
4      [ else default_expr ]
5end
case...end
The case and end keywords begin and close the expression.
when...then
The when and then keywords define a conditional statement. A case expression can contain one or more conditional statement.
  • search_condition—Any logical expression that can be evaluated to true or false. This expression may be constructed using any values, identifiers, logical operator, comparison operator, or scalar functions (including date and math functions) supported by SAQL. Examples of valid search_condition syntax:
    • xInt < 5
    • price > 1000 and price <= 2000
    • units*round(price_per_unit) < abs(revenue)
  • result_expr—Any expression that can be evaluated by the SAQL engine. May contain values, identifiers, and scalar functions (including date and math functions). The expression may evaluate to any data type. However, this data type must be consistent among all conditional expressions. That is, if result_expr is of NUMERIC type, then result_expr2 … result_exprN must be of NUMERIC type. Examples of valid result_expr syntax:
    • xInt
    • toString('orderDate', "dd/MM/yyyy")
    • "abc"
else
(Optional)—Allows a default expression to be specified. The else statement must follow the conditional when/then statement. There can be only one else statement.

  • default_expr—Any expression that can be evaluated by the SAQL engine. May contain values, identifiers, and scalar functions (including date and math functions). The data type must be consistent with the data type of result_expr specified in the preceding conditional statements.

Usage—Searched Case Expression

Conditional statements are evaluated on a row by row basis in the order in which they are given. If a search_condition evaluates as true, the corresponding result_expr is returned for that row. Therefore, if more than one of the conditional statements returns true, only the first one is evaluated. At least one when/then statement must be provided. An unlimited number of when/then statements may be provided.

A default_expr may be set with the optional else statement. If none of the search_condition expressions evaluate to true, the default_expr expression is returned. If no else statement is specified, null is returned as the default.

Syntax—Simple Case Expression

1case primary_expr    
2      when test_expr then result_expr    
3      [ when test_expr2 then result_expr2 … ]    
4      [ else default_expr ]
5end
case...end
The case and end keywords begin and close the expression.
  • primary_expr—Any scalar expression that can be evaluated by the SAQL engine. May contain values, identifiers, and scalar functions (including date and math functions). The expression may evaluate to any comparable data type (NUMERIC, STRING, or DATE). Examples of valid primary_expr syntax:
    • xInt % 3
    • date('year', 'month', 'day')
    • "abc"

A scalar expression takes single values as input and outputs single values. When used with case, the input values can be any expression that is valid in the context of a foreach statement.

Note

when...then
The when and then keywords define a conditional statement. A case expression can contain one or more conditional statements.
  • test_expr—Any scalar expression that can be evaluated by the SAQL engine. This expression may be constructed using any values, identifiers, and scalar functions (including date and math functions), but must evaluate to the same data type as the primary_expr. Examples of valid test_expr syntax:
    • 5
    • "abc"
    • abs(profit)
  • result_expr—Any scalar expression that can be evaluated by the SAQL engine. May contain values, identifiers, and scalar functions (including date and math functions). The expression may evaluate to any data type. However, this data type must be consistent among all conditional statements. That is, if result_expr is of NUMERIC type, then result_expr2...result_exprN must be of NUMERIC type. Examples of result_expr syntax:
    • xInt
    • toString('orderDate', "dd/MM/yyyy")
    • "abc"
else
(Optional) The else keyword allows a default expression to be specified. The else statement must follow conditional when/then statements. There can be only one else statement.
  • default_expr—Any scalar expression that can be evaluated by the SAQL engine. May contain values, identifiers, and scalar functions (including date and math functions). The data type must be consistent with the data type of result_expr specified in the preceding conditional statements.

Usage—Simple Case Expression

Conditional statements are evaluated on a row by row basis in the order that they are given. If primary_expr == test_expr for a given conditional statement, the corresponding result_expr is returned for that row. At least one when/then statement must be provided. An unlimited number of when/then statements may be provided.

A default_expr may be set with the optional else statement. If primary_expr doesn’t equal any of the test_expr conditions, the default_expr is returned. If no else statement is specified, null is returned as the default.

This simple case expression syntax is shorthand for a common instance of the searched case expression syntax. The first block of code is simple case expression syntax and the second block of code is searched case expression syntax. Both blocks of code have the same meaning.

1case primary_expr    
2      when test_expr then result_expr    
3      when test_expr2 then result_expr2    
4else default_expr
1case    
2      when primary_expr == test_expr then result_expr    
3      when primary_expr2 == test_expr2 then result_expr2    
4else default_expr

Tip

Using case Statements

Use case expressions in foreach clauses. Don’t use case expressions in order by, group by, or filter by clauses.

Example

This example query uses the simple case expression syntax: 
1q = load "data";
2q = foreach q generate xInt, (case xInt % 3 
3      when 0 then "3n"     
4      when 1 then "3n+1"    
5      else "3n+2"
6end) as modThree;

Example

This example query uses the searched case expression syntax:
1q = load "data";
2q = foreach q generate price, (case     
3      when price < 1000 then "category1"     
4      when price >= 1000 and price < 2000 then "category2"    
5      else "category3"
6end) as priceLevel;

Handling Null Values

In general, null values can’t be compared. When search_condition, primary_expr, or test_expr evaluates to null, the default_expr specified by else (or null if no else clause is provided) is returned. For instance, the following query returns "Other" whenever Mea1 evaluates to null:
1q = load "data";
2q = foreach q generate Mea1, (case Mea1
3      when 0 then "Type1"     
4      when 1 then "Type2"     
5      else "Other"
6end) as Category;
However, it is possible to specifically a condition on a null value by using the is null and is not null operations.
1q = load “data”;
2q = foreach q generate Mea1, (case     
3      when Mea1 is null then "Is Null"     
4      else "Is Not Null"
5end) as Category;

Best Practices for Working with Dates

Before you use date values in case expressions, use the SAQL toDate() function to convert the date values from strings or Unix epoch seconds. Doing so ensures the most consistent comparisons.

Example

1q = load "data/dates";
2q = foreach q generate OrderDate, (case     
3      when toDate(OrderDate_epoch_secs) < toDate("2/1/2015", "M/d/yyyy") and 
4toDate(OrderDate_epoch_secs) >= toDate("1/1/2015", "M/d/yyyy") then "Jan"     
5      else "Other"
6end) as Month;