Looker offers the ability to write a CASE statement in LookML using the case parameter. This leads to the question of why we should choose to do so when we can write it directly in SQL.

This article compares the LookML case parameter and the pure SQL CASE.

What LookML's case is meant for:

The case parameter in LookML controls the way finite sets of values are presented, ordered, and used in UI filters. For example, using the LookML case parameter creates a drop-down menu for your users in the Looker UI.

What LookML's case is NOT meant for:

• Dimensions with many distinct values desired in the output, which would require you to define each output with a WHEN or ELSE statement.
• When you would like to control labeling of some values, but let others pass through as they exist in the table. LookML's case can't do ELSE field_value; ELSE must be a string.

The Primary Benefits of LookML case

The following are some of the primary use cases where using LookML case is beneficial:

• Converting numeric codes to human-readable labels (same as SQL CASE).
• Specifying a custom ordering of labels based on the order in which you define them. Labels can also be ordered alphabetically by adding the alpha_sort parameter to your field (which is NOT possible with SQL CASE).
• Creating a pick list for filters (also NOT possible with SQL CASE).

Examples

A very common use of SQL CASE is defining a human-readable dimension from a coded field in your database. For example, we can use this for semesters in a school year:

dimension: semester {
  sql: CASE
  WHEN ${TABLE}.status = 0 THEN 'Fall'
  WHEN ${TABLE}.status = 1 THEN 'Winter'
  WHEN ${TABLE}.status = 2 THEN 'Spring'
  ELSE 'Summer'
  END ;;
}

The SQL CASE WHEN will show up in a result alphabetically, rather than in the order the strings were defined in:

• Fall
• Spring
• Summer
• Winter

If a filter is created for the semester dimension, these four values will be suggested to users as they begin to type a filter value.

Now, let's look at these same values in a LookML case:

dimension: semester {
  case: {
    when: {
      sql: ${TABLE}.status = 0 ;;
      label: "Fall"
    }
    when: {
      sql: ${TABLE}.status = 1 ;;
      label: "Winter"
    }
    when: {
      sql: ${TABLE}.status = 2 ;;
      label: "Spring"
    }
    when: {
      sql: ${TABLE}.status = 3 ;;
      label: "Summer"
    }
  }
}

The output of the LookML case above would be ordered as it is in the definition:

• Fall
• Winter
• Spring
• Summer

Custom ordering becomes meaningful in this scenario because, for example, it allows you to calculate continuation rates from one semester to the next. This is something you couldn't do if the semesters were simply ordered alphabetically. Also, if a filter were to be created for this dimension, the user could pick from only these four values.

You can add alpha_sort: yes as a parameter with LookML case to achieve an alphabetic sort of your labels, regardless of the order in which you defined them.

What About Performance?

A LookML case ultimately generates a CASE WHEN in the SQL output. However, an additional CASE WHEN will be generated to maintain the order in which you defined the LookML case. This could be a more expensive operation when querying against a very large table if you have many when subparameters in your LookML case parameter. Nevertheless, if the order of these fields needs to be something other than alphabetical, it is likely you'll need another case statement anyway.