Snowflake general functions | NVL, NVL2, DECODE, COALESCE, NULLIF and NULLIFZERO

 In this article, we’ll be discussing some powerful SQL general functions, which are                                    

                                    1. NVL 

                                    2. NVL2 

                                    3. DECODE 

                                    4. COALESCE 

                                    5. NULLIF 

                                    6. NULLIFZERO

                All these functions work with any data type and pertain to the use of null values in the expression list. These are all single row function i.e. provide one result per row.

                To explain all these functions in detail, will consider the Product table data.   Let's create a table, schema and the product table. After table creation will try to insert some data for demonstration purpose.

Step 1 : Create a database named as DEV_DB.

Step 2 : Create a Schema named as Sales under database DEV_DB.

Step 3 : Create a fact table named as FACT_PRODUCT.

Step 4 : Insert data in the FACT_PRODUCT.

1. NVL  

NVL function can convert a Null value to an actual value. If the value of the first(here Column1) expression then NVL returns the second(here Column2) expression value. 

Limitations :

• NVL function is applicable only to the data types date, character and number .
• Data type must match with each other i.e. expr1 and expr2 must of same data type.

Syntax :

            NVL(Column1,Column2)

Example :

Requirement is to calculate the final price of the product. Final price is calculated as follows :

Final Price = Unit Price - Discount

The column "Discount" has Null values . As we already knew, any operation on the Null results NULL only. So, in this example ,the second value is hardcoded as "0" whenever the first (Discount) value is NULL. 

2. NVL2 :

3. DECODE 

4. COALESCE 

5. NULLIF 

6. NULLIFZERO