How to get SQL DECODE functionality within Dremio – Dremio Support

Summary

Some dialects of SQL use the DECODE syntax as a means to perform IF THEN ELSE logic within a query.

Overview

This article explains how to get the equivalent behavior of the SQL DECODE functionality within Dremio.

Relevant Versions Tools and Integrations

All currently supported Dremio versions.

Steps to Resolve

Certain dialects of SQL , such as Oracle , use DECODE as a means to perform IF THEN ELSE logic in a query .

For example:

SELECT customer_name , DECODE (zip_code , 95123 , 'San Jose CA' 
                                        , 97007 , 'Beaverton OR' 
                                        , 97101 , 'Amity OR'
                                        , 30809 , 'Evans GA' 
                                        , 'Zip Unknown' ) 
FROM customers WHERE customer_id < 10 ;

So for above if the zip_code is 97101 then Amity OR is returned , if the zip_code is not in the list then Zip Unknown is returned.

Dremio does not support DECODE within its SQL language implementation , however you can achieve the same functionality with the ansi sql CASE statement which is available in Dremio.

The previous example becomes :

SELECT customer_name , 
      CASE zip_code WHEN 95123 THEN 'San Jose CA'
                    WHEN 97007 THEN 'Beaverton OR'
                    WHEN 97101 THEN 'Amity OR'
                    WHEN 30809 THEN 'Evans GA'
                    ELSE 'Zip Unknown'
       END 
FROM customers WHERE customer_id < 10 ;

Additional Resources

The syntax for the CASE statement within Dremio can be found in the documentation available here :

https://docs.dremio.com/current/reference/sql/sql-functions/functions/CASE