How to write when and then in oracle

In Oracle, the WHEN and THEN syntax is used with CASE expressions to return different values ​​based on a condition: The WHEN clause specifies the condition to be evaluated. The THEN clause specifies the value to be returned if the condition is true. The ELSE clause (optional) specifies the value returned when all WHEN conditions are false.

Usage of WHEN and THEN syntax in Oracle

WHEN and THEN keywords are used for CASE expressions in Oracle Formula used to return different values ​​under certain conditions.

Syntax:

<code>CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE result_default
END</code>

Usage:

The WHEN clause specifies the condition to be evaluated. If the condition is true, returns the result associated with the WHEN clause.

THEN clause specifies the value or expression to return if the condition is true.

The ELSE clause is optional, and if all WHEN conditions are false, the value or expression associated with the ELSE clause is returned.

Example:

<code class="sql">SELECT CASE
  WHEN salary > 5000 THEN 'High'
  WHEN salary > 3000 THEN 'Medium'
  ELSE 'Low'
END AS salary_category
FROM employees;</code>

This query will classify an employee's salary as "High", "Medium" or "Low" based on their salary.

Key Points:

• WHEN conditions can use any valid Oracle Boolean expression.
• THEN The result can be of any data type, including scalar values, expressions, or subqueries.
• The ELSE clause can only specify one result.
• If no WHEN conditions are met, the results in the ELSE clause (if specified) are returned.
• CASE expressions can be nested to create more complex results based on multiple conditions.

The above is the detailed content of How to write when and then in oracle. For more information, please follow other related articles on the PHP Chinese website!