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

Example 2: The CASE Expression Using a Value

Table: WORLDTEMPS

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 Expression

Syntax

Arguments

case-expression

when-expression