logo

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




Next Lesson

Transforming with String-Text Functions