SQL differences between Snowflake & Oracle db

   Concat () and Concat operator:

 

The Oracle CONCAT function concatenates two strings and returns the combined string.

Oracle:

Syntax:

CONCAT (string1 || string2 ||....)

CONCAT(<expr1> [, <exprN> ...])

Ex:

1)     select concat('SSS',null) from dual

Result: SSS

      2)  select 'SSS' || null from dual

           Result:  SSS

Snowflake:

Syntax:

CONCAT (string1 || string2 ||....)

CONCAT(<expr1> [, <exprN> ...])

Ex:

1)     select concat('SSS',null) from dual

Result: null

      2)  select 'SSS' || null from dual

            Result:  null

 

Issue: In Snowflake, if any part of the concatenation is null, the entire result is null.

To fix this in Snowflake, use NVL functions:

1)     Select concat(nvl('SSS',''), nvl(null,'')) from dual

Result:  SSS

 

2)     select nvl('SSS,'') || nvl(null,'') from dual

Result:  SSS

Ø INSTR ()

 

The Oracle INSTR () function searches for a substring in a string and returns the position of the substring in a string.

Oracle:

Syntax:

INSTR (string, substring [, start_position [, occurrence]])

Ex:

1)     INSTR ('Tech on the net', 'e')

Result: 2 (the first occurrence of 'e')

 

2)     INSTR ('Tech on the net', 'e', 1, 3)

Result: 14 (the third occurrence of 'e')

 

3)     INSTR ('Tech on the net', 'e', -3, 2)

Result: 2

4)     SELECT INSTR ('opal kole', 'l', 6, 1) "INSTR" FROM DUAL;

 

Result:   INSTR

               ----------

                  8

Note:

No Instr () function in snowflake. Instead of Instr () in snowflake, Position () is used.

Ø Position ()

Searches for the first occurrence of the first argument in the second argument and,

if successful, returns the position (1-based) of the first argument in the second argument.

Syntax:

POSITION(<expr1>, <expr2> [, <start_pos> ] )

Ex:

1)     select position ('e', 'Tech on the net') from dual

Result: 2

2)     select position ('e', 'Tech on the net',12) from dual

Result: 14

3)     select position ('e', 'Tech on the net’, -3) from dual

Result: 2

4)     select position ('l', 'opal kole',6) from dual

Result: 8

Ø Date Diff function:

Oracle allowed subtracting one date from another, resulting in a difference in days. In Snowflake, you can only do this with date fields, not timestamp.

The Fix:

Instead, you must use the DATEDIFF or TIMESTAMPDIFF function.

Oracle:

MONTHS_BETWEEN function returns the number of months between date1 and date2.

Syntax:

MONTHS_BETWEEN(date1, date2 )

Snowflake:

DATEDIFF(<date_or_time_part>, <date_or_time_expr1>, <date_or_time_expr2>)

Ex:

select datediff(year, '2010-04-09 14:39:20'::timestamp, '2013-05-08 23:39:20'::timestamp)  as diff_years;

 

Result: 3

 

Ø Date Trunc

 

Date trunc() function rounds the date as per the given arguments i.e "dd", "month", "year".

 ex- when you actually wants to look at the date value itself trunc function is used to roundoff the timestamp values i.e HH:MM: SS: F9 and displays only date value (2020-03-10).

 

Syntax:

 

TRUNC (date [, format])

 

Note:

In Oracle, you could use the trunc() function to truncate the time from a date field: trunc(sysdate) would return today’s date at midnight.

In Snowflake, the trunc function must also include the level to truncate to.

The Fix:

trunc(current_timestamp,’DD’)

Ø Outer Joins

Snowflake does not allow outer joins using the (+) syntax.

It requires Ordered Joins. Statements must be written using ANSI-SQL like “left outer join”, “right outer join”, etc.