In CASE You Want to Use Logic in Snowflake

Sometimes you need to check one field to see if you want to display another field. Sometimes you show one of two fields depending on the value in yet another field. Sometimes you just need to relax…

Today, though, we’re going to go thru a myriad of functions that you can use in Snowflake to get the above to happen (well, maybe not the “relax” bit – though the functions do make life a little bit easier.)

For this example, I had to create some new data that showed what I needed so I’ll be using the employee data found here. I created a new table called NewEmployee for this batch of goodness.

First, the ubiquitous CASE statement:

SELECT 
    EEID, 
    CASE 
        WHEN ExitDate IS NOT NULL THEN 'Active'
        ELSE 'Inactive' 
    END AS IsActive
FROM NewEmployee;

From this, we can quickly tell whether all of our employees work for our company and whether or not they are active.

If I want to be able to more easily read this code, I can use the IFF function. Since it’s only one line that is fairly short, this is a winner to me.

Now, if I want to get really fancy, since the data we’re checking in the ExitDate field is null or not, I can use the Snowflake function NVL2.

Notice that the way that I have the IFF and the NVL2 statements setup are the same. For the NVL2 statement, if the initial logic is NOT NULL, then it will show the 2nd parameter otherwise, the 3rd parameter.

That, my friend, is a developers dream! Three different ways to get to the same solution. Are there drawback to any of them? The answer –

For the CASE statement – the initial condition can include set operations such as UNION, INTERSECT, or EXCEPT.

Same thing goes for the IFF statement. But something to concern yourself about with IFF that you didn’t have to for CASE is the collation. The highest-precedence collation of the THEN/ELSE arguments is the winner for the IFF statement.

On the other hand, NVL2 requires that all of the parameters be of the same, or compatible, data types. Also, in the case of NVL2, the collation only matters between the 2nd and 3rd parameters. The collation is also the highest-precedence collation between those two.

With that, I hope you enjoy using some new functions in Snowflake and how to make them easier to work with and troubleshoot. This week, I thought I’d go out with one of my favorites – Mary J Blige – and Case’s big song of 1996 Touch Me Tease Me.