This might seem hard to understand, so let's dig into our example query: SELECT name, weight, If all conditions return FALSE, then the result from ELSE is displayed. This will cause the result from THEN to display. These conditions will compare a retrieved value and return the first one it meets as TRUE. But after the WHEN keyword, we enter one or more conditions rather than set values. There can be multiple WHEN – THEN constructions, just like the simple CASE. Once again, the expression begins with the CASE keyword, but there is no column name or expression following. Here's the syntax: CASE WHEN conditions THEN when_result This makes a searched CASE the better choice for more complicated logic. But instead of comparing a column or expression against a defined set of values, a searched expression can compare multiple WHEN conditions and determine a result. The searched CASE expression is the most commonly-used format. The continent is based on the country column, which uses the CHARACTER data type. This query selects data from the table and displays the continent where the animal comes from. You can use the CASE expression with other data types, such as CHARACTER. The above query, now with an alias, looks like this: SELECT name, zoo, The CASE expression can have an alias, or a name for the resulting new column.
#PUREBASIC SELECT CASE CODE#
Here's the same code without an ELSE: SELECT name, weight, number_of_animal, Without the ELSE, all non-matching weights would display a NULL. This CASE expression has an ELSE clause that displays other. Here is the query: SELECT name, weight, number_of_animal, If the total weight is not one of these three numbers, then other will be shown. In this case, three characteristic numbers must be determined.
Imagine that, besides animals' weights in kilograms, you would also like to display the total weight of mammals by species – but only if these values are special. You can also put an expression after the CASE keyword. Up til now, we've considered simple CASE expressions that have a column name after the CASE keyword. Notice that if the value from the zoo column is not the same as either of the values in the WHEN clauses (or is NULL), the result is unknown. If zoo=1, then Brookfield Zoo will be displayed if zoo=2 then we'll see Dublin Zoo all other cases will display as unknown. This example selects data from the animal table and displays the name of the animal's current zoo. The following example queries are based on data from the animal table, as shown below: id If the value from the column or expression doesn't match any WHEN values, then the result of ELSE is displayed. If the evaluated value is the same the as the set value, the result defined in THEN is returned.
In a simple CASE expression, the name of the column or expression to be evaluated is absolutely necessary. Here's what the syntax looks like: CASE column_or_expression WHEN value THEN when_result If it meets a WHEN condition, the THEN result is returned.
The simple CASE compares a value to one or more WHEN conditions. Each one has a slightly different syntax, and each works a little differently. There are two types of CASE expressions: simple and searched. If ELSE is not defined and WHEN is not met, a NULL will be returned.
An optional ELSE statement determines the result returned if the WHEN condition is not met. After the WHEN clause, we see the condition or value to compare the THEN determines the result if the condition is met. The WHEN and THEN keywords define the logical conditions. The names of specific columns or expressions are entered after the CASE keyword. The expression starts with the CASE keyword and ends with the END keyword. The basic syntax of the CASE expression is presented below: CASE