Tech-Discussion

    TRANSLATE If there are unwanted characters in the database and you want to replace or remove those unwanted characters.  If you see here, the actual string is " SQL Coding is Fun " but somehow there are unwanted and the string is saved as " SQL x Codi y ng is z Fun ".  We need to remove additional characters x,y & z. MS-SQL Server   You can achieve this by using  REPLACE . But you need to take multiple iterations. In  Snowflake ,  we can use a similar syntax as above; h owever, Snowflake provides an alternative  TRANSLATE .     By using this we can achieve a single iteration. For more details, you can refer to the following  TRANSLATE .  Yogesh Shinde LinkedInProfile <<< Back   Next >>>      

    CONCAT Suppose you have a table and it is storing department ID as below.  +-------------+------------+------------+---------------+ | EMPLOYEE_ID | LAST_NAME | FIRST_NAME | DEPARTMENT_ID | |-------------+------------+------------+---------------| | 101 | Montgomery | Pat | 1 | | 102 | Levine | Terry | 2 | | 103 | Comstock | Dana | 2 | +-------------+------------+------------+---------------+ As per requirement, you want to display the department ID as Dept-1. +-------------+------------+------------+---------------+ | EMPLOYEE_ID | LAST_NAME | FIRST_NAME | DEPARTMENT_ID | |-------------+------------+------------+---------------| | 101 | Montgomery | Pat | DEPT-1 | | 102 | Levine | Terry | DEPT-2 | | 103 | Comstock | Dana | DEPT-2 | +-------------+------------+------------+---------------+ MS-SQL Server ...

    Lateral Join Suppose you have two tables as below: And you need to pull the data from both tables as below: MS-SQL Server   There are multiple approaches to achieve this. The common one is to use Inner Join. Another alternate is CROSS APPLY as below. In  Snowflake ,  we can not use CROSS APPLY as it does not support it. However, it provides an equivalent of it i.e.  LATERAL JOIN.   A lateral join behaves more like a correlated sub-query as below: For more details, you can refer to the following  LATERALJOIN .  Note: However MS-SQL Server provides you OUTER APPLY additionally and Snowflake does not have an equivalent of it, you may need to use LEFT JOIN. Yogesh Shinde LinkedInProfile <<< Back   Next >>>     

    QUALIFY You might be using the Windows function for different purposes and need to filter this result. MS-SQL Server   If you want to filter in this situation then you need to use an outer query to filter it out . In  Snowflake ,  we can use a similar code as above or we can make use of QUALIFY to avoid an additional outer query. Also, you can use the Windows function to Qualify as below. For more details, you can refer to the following  QUALIFY . Yogesh Shinde LinkedInProfile <<< Back   Next >>>    

    Variables During coding, we define the variables and store the value in those variables. We can use those variables later on. MS-SQL Server   You can use the  DECLARE keyword to define the variables and either SELECT or SET clause to assign the value to those variables . In  Snowflake , we can go with a similar approach. For more details, you can refer to the following  Variables . Yogesh Shinde LinkedInProfile <<< Back   Next >>>   

    Column Alias Sometimes you have to extract the data from the database and share it in Excel and hand it to a user or someone outside. In this case, you have to ensure that you do not expose confidential details like table name or column name. MS-SQL Server   You can use the alias for the column name . But suppose the table has more columns like 20+ then in this case to make an alias for one column you have to list out the names of all columns and you may fill this is tedious. Here we wanted to make an alias for Column_2, we have to list out ALL columns. In  Snowflake , we don't need to mention ALL column names as above. We can use the keyword  Rename. If you see here, I wanted to hide the SSN column and alias it as code. I can use the Rename keyword along with Select *. In case, you want to provide an alias for multiple columns then you can use as follows: For more details, you can refer to the following  Rename_1   or  Rename_2 . Yogesh Shind...

    Drop Stored Procedure MS-SQL Server   You can use a simple command drop procedure to drop stored procedure as below . In  Snowflake , if we use the same command above, it will throw an error. You need to specify the data type of the stored procedure's input parameter along with the stored procedure's name.    If you have multiple input parameters to the stored procedure, you must specify all those data types. Similarly, if you want to drop multiple stored procedures then you can specify them separated by a comma in the MS-SQL server. However, this approach does not work in Snowflake. You will have to drop stored procedures one by one. For more details, you can refer to the following  DropProcedure . Yogesh Shinde LinkedInProfile <<< Back   Next >>>