Snowflake Vs MS-SQL - Part 41 - NVL2

         


NVL2

A table contains 3 columns, if the value in the first column is NOT NULL then you have to return/display the value from the second column and if the value in the first column is NULL then you have to return/display the value from the third column.

Suppose you have data as below:
--------+--------+--------+
Col1    | Col2   | Col3   |
--------+--------+--------+
 0      | 5      | 3      |
 0      | 5      | [NULL] |
 0      | [NULL] | 3      |
 0      | [NULL] | [NULL] |
 [NULL] | 5      | 3      |
 [NULL] | 5      | [NULL] |
 [NULL] | [NULL] | 3      |
 [NULL] | [NULL] | [NULL] |
--------+--------+--------+

Expected Output
--------+--------+--------+---------------+
Col1    | Col2   | Col3   | RESULT        |
--------+--------+--------+---------------+
 0      | 5      | 3      | 5             |
 0      | 5      | [NULL] | 5             |
 0      | [NULL] | 3      | [NULL]        |
 0      | [NULL] | [NULL] | [NULL]        |
 [NULL] | 5      | 3      | 3             |
 [NULL] | 5      | [NULL] | [NULL]        |
 [NULL] | [NULL] | 3      | 3             |
 [NULL] | [NULL] | [NULL] | [NULL]        |
--------+--------+--------+---------------+

MS-SQL Server 

You can achieve this by using the CASE Statement as follows. 



In Snowflake, we can use the same approach as above however Snowflake has a different conditional expression NVL2.


Note: If you want to display static values instead of Col2 & & Col3, then it also supports.
You can use nested as well.


For more details, you can refer to the following NVL2.




Comments

Post a Comment

Popular posts from this blog

Snowflake Vs MS-SQL - Part 36 - SPLIT_TO_TABLE

Image
       SPLIT_TO_TABLE Sometimes we get a string with commas separated and we need to convert it into rows as below: Suppose you receive a string in this format ' 1,2,3,4,5 ' And you want to return like this. MS-SQL Server   You can achieve this by using multiple ways.  In the recent version of MS SQL Server, they came up with a new String Function  STRING_SPLIT . In  Snowflake ,  we can use a similar function  SPLIT_TO_TABLE  as  STRING_SPLIT.   For more details, you can refer to the following  SPLIT_TO_TABLE .  Yogesh Shinde LinkedInProfile <<< Back   Next >>>
Read more

Snowflake Vs MS-SQL - Part 63 - ON Clause & Cross Join

Image
          ON Clause & Cross Join We use different joins to pull the records from multiple tables. We need to use the ON clause along with JOIN. What will happen if we don't use the ON clause? MS-SQL Server   If we don't mention the ON clause, it throws an error. However, if we use the same column from the same table on both sides, it works as a cross-join.  Also, if you mention condition as 1=1 in the ON clause, it treats as CROSS JOIN. However, in  Snowflake,  both above queries will work.  Apart from this, if you don't mention the ON clause, it treats it as CROSS JOIN: Yogesh Shinde LinkedInProfile <<< Back   Next >>>   
Read more

Snowflake Vs MS-SQL - Part 33 - INITCAP

Image
      INITCAP Sometimes we need to provide strings/lines in a specific format as per the client's request. Suppose you want to capitalize the first letter of each word in the string and all other letters appear in lowercase.  It needs special treatment, known as  InitCap (Initial Capitalisation). MS-SQL Server   You can only achieve this differently. You need to write a function or couple of lines of code as below:  In  Snowflake ,  we don't need to write so many lines of code . We can use the inbuilt function  INITCAP. Also, this function gives you the flexibility to provide a delimiter  that INITCAP uses as a separators for words in the input expression.  In the below example, 'q' is used as the delimiter. For more details, you can refer to the following  INITCAP .  Yogesh Shinde LinkedInProfile <<< Back   Next >>>  
Read more