CASE Expression

Selects result values that satisfy search conditions and value comparisons.

Syntax

Arguments

case-expression

specifies any valid SQL expression that evaluates to a table column whose values are compared to when-expression.

See <sql-expression>
FedSQL Expressions

when-expression

specifies any valid SQL search condition expression or a value expression.

  • When case-expression is not specified, when-expression is a search condition expression that evaluates to true or false.
  • When case-expression is specified, when-expression is an SQL value expression that is compared to case-expression and that evaluates to true or false.
See <sql-expression>

result-expression

specifies an SQL expression that evaluates to a value.

See <sql-expression>

Details

The CASE expression selects values if certain conditions are met. The case-expression argument returns a single value that is conditionally evaluated for each row of a table. Use the WHEN-THEN clauses to execute a CASE expression for some, but not all of the rows in the table that is being queried or created. The optional ELSE expression gives an alternative action if no THEN expression is executed.
When you omit case-expression, when-expression is evaluated as a Boolean (true or false) value. If when-expression returns a nonzero, non-null result, then the WHEN clause is true. If case-expression is specified, then it is compared with when-expression for equality. If case-expression equals when-expression, then the WHEN clause is true.
If the when-expression is true for the row that is being executed, then the result-expression that follows THEN is executed. If when-expression is false, then FedSQL evaluates the next when-expression until they are all evaluated. If every when-expression is false, then FedSQL executes the ELSE expression, and its result becomes the CASE expression's result. If no ELSE expression is present and every when-expression is false, then the result of the CASE expression is null.
You can use a CASE expression as an item in the SELECT clause and as either operand in an SQL expression.

Comparisons

The COALESCE expression and the NULLIF expression are variations of the CASE expression.
The following CASE expression and COALESCE expression are equivalent:
case
   when value1 is not null
      then value1
   when value2 is not null
      then value2
   else value3
end
coalesce(value1, value2, value3)
The following CASE expression and NULLIF expression are equivalent:
case
   when value1 = -1 then null
   else value1
end
nullif(value1, -1);

Examples

Example 1: The CASE Expression Using A Search Condition

Table: WORLDTEMPS
select AvgLow,
   case
      when AvgLow < 32 then AvgLow + 2
      when ((AvgLow < 60) and (AvgLow > 32)) then AvgLow + 5
	    when AvgLow > 60 then AvgLow + 10
	    else AvgLow
   end
 as Adjusted from worldtemps;
SAS creates the follow table:
CASE Using a Search Condition
CASE Using a Search Condition

Example 2: The CASE Expression Using a Value

select Country,
   case Country
      when 'Algeria' then 'Africa'
      when 'Nigeria' then 'Africa'
      when 'Netherlands' then 'Europe'
      when 'Spain' then 'Europe'
      when 'Switzerland' then 'Europe'
      when 'China' then 'Asia'
      when 'India' then 'Asia'
      when 'Venezuela' then 'South America'
       else 'Unknown'
   end
as Continent from worldtemps;
SAS creates the following table:
CASE Using a Value
CASE Using a Value

See Also

<search-condition> in the SELECT Statement
Last updated: February 23, 2017