SQL differences between Snowflake & Oracle db.
Concatenation operator:
While we were running & repointing our loads into Snowflake we discovered a important difference in how Oracle vs Snowflake concatenation works.
Any fields using concatenation likely need to be modified.
In Snowflake, if any part of the concatenation is null, the entire result is null.
To fix this in Snowflake, use NVL functions:
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.
Date Trunc
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’)
INSTR Function
Instr function replaced with REGEXP_INSTR
Snowflake does not have INSTR() function.
The Fix:
Replace with REGEXP_INSTR() or POSITION().
See Snowflake documentation:
https://docs.snowflake.net/manuals/sql-reference/functions-regexp.html
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.
Make sure your mappings are set to use ANSI Syntax and have a Join Order set:
Will add more as we discover them.
