How to use a CASE function (SQL) to populate tables
One of the most important tabular functions is the ability to do a selection of a value in an existing column and populate a new column with a specific value. With the CASE function, the user can reclassify existing data sets or add new descriptions in a column based on a specific conditions set by the user.
CASE is used to provide if-then-else type of logic to SQL. There are two formats: The first is a Simple CASE expression, where we compare an expression to static values. The second is a Searched CASE expression, where we compare an expression to one or more logical conditions.
Simple CASE expressions:
The syntax for a simple CASE expression is this:
CASE [column_name]
WHEN 'value1' THEN 'result1'
WHEN 'value2' THEN 'result2'
WHEN 'value3' THEN 'result3'
ELSE 'Other'
END
(Please note that the ELSE clause is optional)
For example, assume we have the following Land Cover table,
If we want to add descriptive names in the new column called "Class name", we would use the following SQL statement using CASE:
First click on the column name dropdown button to access the field calculator
The CASE statement will look like the following:
CASE [GRIDCODE]
WHEN 1 THEN 'Cultivated comm fields (high)'
WHEN 2 THEN 'Cultivated comm fields (medium)'
WHEN 3 THEN 'Cultivated comm fields (low)'
ELSE 'Other'
END
This syntax needs to be put in the expression box of the field calculator and will look like this
This will result in new values in the "Class name" column:
Searched CASE expression:
The syntax for a searched CASE expression is this:
CASE
WHEN [column_name] 'condition1' THEN 'result1'
WHEN [column_name] 'condition2' THEN 'result2'
ELSE 'Other'
END
(Please note that the ELSE clause is optional)
If we want to add descriptive names in the new column called "Class name", based on a specific condition, we would use the following SQL statement using CASE:
First click on the column name dropdown button to access the field calculator
The CASE statement will look like the following:
CASE
WHEN [GRIDCODE] < 2 THEN 'Cultivated comm fields (high)'
WHEN [GRIDCODE] > 1 THEN 'Cultivated comm fields (medium/low)'
ELSE 'Other'
END
This syntax needs to be put in the expression box of the field calculator and will look like this
This will result in new values in the "Class name" column:
