|
|
The features on this page require a GENESIS64 Advanced license and are not available with GENESIS64 Basic SCADA . |
|
|
The features on this page require a GENESIS64 Advanced license and are not available with GENESIS64 Basic SCADA . |
Evaluates a list of conditions and returns one of multiple possible result expressions. The CASE expression has two formats:
· Simple CASE, which compares an expression to a set of simple expressions to determine the result
· Searched CASE, in which a set of Boolean expressions are evaluated to determine the result
CASE can be used in anywhere an expression can be used, like in a SELECT list, in a WHERE statement, ORDER BY, etc.
The simple CASE expression operates by sequentially comparing the first expression to the expression in each WHEN clause for equivalency. If the expressions are equivalent, the expression in the THEN clause will be returned.
Simple CASE supports an optional ELSE clause which allows to specify a default value to return if none of the expressions in the WHEN clauses is equivalent to the first expression. When the ELSE clause is not provided, NULL is returned when none of the expressions in the WHEN clauses is equivalent to the first expression.
The following query returns all products with a string description on whether the product has been discontinued or not. In this example the first expression (the column Discontinued) gets compared with the Boolean values false and then true in order to produce the result in the THEN clause.
SELECT ProductName,
CASE Discontinued
WHEN false THEN 'Available'
WHEN true THEN 'Discontinued'
END AS Status
FROM Products
The following query uses the ELSE clause to return a UnitPrice discounted by 10% for all beverages, a UnitPrice increased by 5% for all seafood and an unmodified UnitPrice for any other product type.
SELECT ProductName, UnitPrice,
CASE CategoryID
WHEN 1 THEN UnitPrice * 0.90
WHEN 8 THEN UnitPrice * 1.05
ELSE UnitPrice
END AS ModifiedUnitPrice
FROM Products
The searched CASE expression operates by sequentially evaluating the Boolean expressions in each WHEN clause. If an expression evaluates to true, the expression in the THEN clause is returned.
Searched CASE supports an optional ELSE clause which allows to specify a default value to return if none of the expressions in the WHEN clauses evaluate to true. When the ELSE clause is not provided, NULL is returned when none of the expressions in the WHEN clauses evaluate to true.
The following query returns the fax number for all customers and defaults to the string “No Fax” for customers that do not have a fax number.
SELECT ContactName,
CASE
WHEN Fax IS NULL THEN 'No Fax'
ELSE Fax
END
FROM Customers