Learning Snowflake SQL and Scripting

Chapter 10. Conditional Logic

In certain situations, you may want your SQL statement to behave differently depending on the values of certain columns or expressions, which is known as conditional logic. The mechanism used for conditional logic in SQL statements is the case expression, which can be utilized in insert, update, and delete statements, as well as in every clause of a select statement.

What Is Conditional Logic?

Conditional logic is the process by which one of several paths can be taken. For example, a company’s order entry system may have logic specifying that a 10% discount be given if that customer’s orders exceeded a certain value for the previous year. If you have written programs using a language like Python or Java, you are accustomed to using if...then...else statements, but the SQL language uses case expressions for conditional logic. The case expression works like a cascading if-then-else statement, evaluating a series of conditions in sequence. Here’s a simple example:

PUBLIC>select c_custkey, c_name, c_acctbal,
         case
           when c_acctbal < 0 then 'generate refund'
           when c_acctbal = 0 then 'no action'
           else 'send bill'
         end as month_end_action        from customer        limit 15; +-----------+--------------------+-----------+------------------+ | C_CUSTKEY | C_NAME             | C_ACCTBAL | MONTH_END_ACTION | |-----------+--------------------+-----------+------------------| |     60001 | Customer#000060001 |   9957.56 | ...