Manipulating with CASE

This tutorial teaches you how to shape, transform, and manipulate data using the statement.

Syntax

The statement goes through one or several conditions and returns a value when the first condition is met.

• If the first condition is true, it returns the result specified after the clause.
• If the first condition is not true, it evaluates the next condition in the statement.
• If all the conditions are not true, it ends with an clause or an clause that returns a specified value.

After the clause, be sure to give it an alias to declare it as a newly created column.

Example 1: CASE WHEN ... THEN

The following query assigns fruit as a product category when the product is an apple, banana or cherry.

Example 2: CASE WHEN ... THEN x2

The following query assigns fruit as a product category when the product is an apple, banana or cherry and beverage when the product is a coffee.

Example 3: CASE WHEN ... THEN ... ELSE

The following query assigns to the product category:

• 'fruit' when the product is an apple, banana or cherry, and
• 'beverage' when the product is a coffee.

Otherwise, assign a string saying "Can you identify the category?" for further identification of the product.

Test Your Knowledge with DataLemur Question