Tech-Discussion

           Search Data A table contains multiple columns and if you want to search a particular word in any one of these columns. Suppose you have data as below: + ---------------------------+-----------------+ | PLAY                      | CHARACTER       | | ---------------------------+-----------------| | All Well That Ends Well   | KING            | | Henry IV Part 1           | KING CLAUDIUS   | | John John                 | CHATILLON       | | King John                 | KING JOHN       | | Lear Lear                 | GLOUCESTER      | + ---------------------------+-----------------+ Expected Output + ---------------------------+----...

          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   ...

         Stored Procedure Call If you want to retrieve the data by calling a Stored Procedure. Suppose you are selecting a table inside a Stored Procedure and want to get the result using Stored Procedure. MS-SQL Server   You can achieve this by using the following approach.  In  Snowflake,  we can use a different approach . For more details, you can refer to the following  StoredProcedure . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

        EQUAL_NULL Your data is as below, if you make self join then it should return 3 records instead of 2 records by considering NULL.  Expected Output: MS-SQL Server   You can achieve this by using the  ISNULL  function.  In  Snowflake  we can use the similar function  IFNULL. However, Snowflake has another two approaches  which give you the same result. Function  EQUAL_NULL   Operator  IS [ NOT ] DISTINCT FROM   EQUAL_NULL is a function, while IS [ NOT ] DISTINCT FROM is an operator. EQUAL_NULL is used as a function call, while IS [ NOT ] DISTINCT FROM is used as part of a SQL expression. EQUAL_NULL: A function to compare two values, treating NULL as equal. IS [ NOT ] DISTINCT FROM: An operator to compare two values, treating NULL as equal or distinct based on the context. For more details, you can refer to the following  EQUAL_JOIN   or  IFNULL   or  IS_DISTINCT_FROM ...

        IFNULL/NVL Your data may often contain a NULL value and you need to display or replace it with some other value. As below, for NULL we are replacing it by Default . MS-SQL Server   You can achieve this by using the  ISNULL  function.  In  Snowflake  we can use the similar function IFNULL. However, Snowflake has another function  NVL  which gives you the same result. For more details, you can refer to the following  NVL   or  IFNULL . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

       GROUP BY ALL Suppose you have the data as below: If you want to group on this dataset as below: MS-SQL Server   You can achieve this by using the  Group By clause.  In  Snowflake  we can use the same Group By clause; however, we can skip the list of tables. This will be helpful if you have fewer columns in Group By clause. For more details, you can refer to the following  Group_By_All .  Yogesh Shinde LinkedInProfile <<< Back   Next >>>

       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 >>>

        iLike ANY If you have a dataset as below and want to make a comparison to match a string against data with a wild card like 'o%' or 'f%': Expected Result: MS-SQL Server   You can do this using a like clause along with OR as below:  In  Snowflake ,  we can go with a similar approach, however, w e can use the iLike and flexibility of ANY. For more details, you can refer to the following  iLike_Any .  Yogesh Shinde LinkedInProfile <<< Back   Next >>>  

       NTH_VALUE If you have a dataset and you want to calculate the third highest value in each group as below: + ---------+---------+ | COLUMN1 | COLUMN2 | | ---------+---------+ |       1 |       10 | |       1 |       11 | |       1 |       12 | |       1 |       20 | |       1 |       21 | |       1 |       22 | |       2 |       30 | |       2 |       31 | |       2 |       32 | |       2 |       40 | |       2 |       41 | |       2 |       42 | + ---------+---------+ Expected Result: + ---------+---------+ | COLUMN1 | COLUMN2 | | ---------+---------+ |       1 | ...

      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 >>>  

      Else IF Many times you have to compare the equation and then make the decision. Suppose the value is greater than 100 then it should print " The number is large ". if it is less than 10 then it should print " The number is small " and for all the remaining it should print " The number is medium ". MS-SQL Server   You can achieve this by using the  IF ELSE statement.  In  Snowflake ,  we can use a similar IF Else but it has a variant ELSEIF as below .   For more details, you can refer to the following  ELSEIF .  Yogesh Shinde LinkedInProfile <<< Back   Next >>>