CASE

Conditional expressions in GemFire XD.

Syntax

GemFire XD supports only the searched CASE syntax as follows:

CASE 
  WHEN booleanExpression THEN thenExpression 
  [ WHEN booleanExpression THEN thenExpression ]...
  ELSE elseExpression 
END
Note: GemFire XD does not support simple CASE expressions such as: CASE valueExpression WHEN valueExpression THEN thenExpression ELSE elseExpression END

You can place a searched CASE expression anywhere an expression is allowed. It chooses an expression to evaluate based on a boolean test.

thenExpression and elseExpression are both expressions that must be type-compatible. For built-in types, this means that the types must be the same or a built-in broadening conversion must exist between the types.

Example

-- returns 3
VALUES CASE WHEN 1=1 THEN 3 ELSE 4 END

-- returns 7
VALUES
   CASE
      WHEN 1 = 2 THEN 3
      WHEN 4 = 5 THEN 6
      ELSE 7
   END

-- explicitly cast expression results when using a thin client connection
SELECT CAST (
         CASE 
           WHEN customer.id_str = '41658296553177088' then 'matched' 
           ELSE 'other' 
         END 
       AS VARCHAR(10)) 
       FROM customer WHERE customer.id > 41658296553177086 and customer.id < 41658296553177090