In this section you could find information about the select query syntax and its convertions.
Some parts in the output codes are omitted for clarity reasons.
Overall Select Translation
Simple select
Input Code:
IN -> Oracle_01.sql
select * from table1;
select col1 from schema1.table1;
Output Code:
OUT -> Oracle_01.sql
select * from
table1;
select col1 from
schema1.table1;
Where clause
Input Code:
IN -> Oracle_02.sql
select col1 from schema1.table1 WHERE col1 = 1 and id > 0 or id < 1;
Output Code:
OUT -> Oracle_02.sql
select col1 from
schema1.table1
WHERE col1 = 1 and id > 0 or id < 1;
Order By clause
Input Code:
IN -> Oracle_03.sql
select col1 from schema1.table1 order by id ASC;
Output Code:
OUT -> Oracle_03.sql
select col1 from
schema1.table1
order by id ASC;
Group by
Input Code:
IN -> Oracle_04.sql
select col1 from schema1.table1 GROUP BY id;
Output Code:
OUT -> Oracle_04.sql
select col1 from
schema1.table1
GROUP BY id;
Model Clause
The model clause is not supported yet.
Row Limiting Clause
Input Code:
IN -> Oracle_05.sql
-- Using ONLY
select * from TableFetch1 FETCH FIRST 2 ROWS ONLY;
select * from TableFetch1 FETCH FIRST 20 percent ROWS ONLY;
select * from TableFetch1 order by col1 FETCH FIRST 2 ROWS with ties;
select * from TableFetch1 order by col1 FETCH FIRST 20 percent ROWS with ties;
-- Using OFFSET clause
select * from TableFetch1 offset 2 rows FETCH FIRST 2 ROWS ONLY;
select * from TableFetch1 offset 2 rows FETCH FIRST 60 percent rows ONLY;
select * from TableFetch1
order by col1 offset 2 rows FETCH NEXT 2 ROWs with ties;
select * from TableFetch1
order by col1 offset 2 rows FETCH FIRST 60 percent ROWs with ties;
-- Using WITH TIES clause
select * from TableFetch1 FETCH FIRST 2 ROWS with ties;
select * from TableFetch1 FETCH FIRST 20 percent ROWS with ties;
select * from TableFetch1 offset 2 rows FETCH NEXT 2 ROWs with ties;
select * from TableFetch1 offset 2 rows FETCH FIRST 60 percent ROWs with ties;
-- Using ORDER BY clause
select * from TableFetch1 order by col1 FETCH FIRST 2 ROWS ONLY;
select * from TableFetch1 order by col1 FETCH FIRST 20 percent ROWS ONLY;
select * from TableFetch1 order by col1 offset 2 rows FETCH FIRST 2 ROWS ONLY;
select * from TableFetch1
order by col1 offset 2 rows FETCH FIRST 60 percent ROWS ONLY;
select * from TableFetch1 FETCH FIRST ROWS ONLY;
select * from TableFetch1 offset 2 rows;
Output Code:
OUT -> Oracle_05.sql
-- Using ONLY
select * from
TableFetch1
FETCH FIRST 2 ROWS ONLY;
select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
ORDER BY
NULL) - 1) / COUNT(*) OVER () < 20 / 100;
select * from
TableFetch1
QUALIFY
RANK() OVER (
order by col1) <= 2;
select * from
TableFetch1
QUALIFY
(RANK() OVER (
order by col1) - 1) / COUNT(*) OVER () < 20 / 100;
-- Using OFFSET clause
select * from
TableFetch1
offset 2 rows FETCH FIRST 2 ROWS ONLY;
select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
ORDER BY
NULL) - 1 - 2) / COUNT(*) OVER () < 60 / 100
LIMIT NULL OFFSET 2;
select * from
TableFetch1
QUALIFY
RANK() OVER (
order by col1) - 2 <= 2
LIMIT NULL OFFSET 2;
select * from
TableFetch1
QUALIFY
(RANK() OVER (
order by col1) - 1 - 2) / COUNT(*) OVER () < 60 / 100
LIMIT NULL OFFSET 2;
-- Using WITH TIES clause
select * from
TableFetch1
FETCH FIRST 2 ROWS ONLY;
select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
ORDER BY
NULL) - 1) / COUNT(*) OVER () < 20 / 100;
select * from
TableFetch1
offset 2 rows FETCH NEXT 2 ROWS ONLY;
select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
ORDER BY
NULL) - 1 - 2) / COUNT(*) OVER () < 60 / 100
LIMIT NULL OFFSET 2;
-- Using ORDER BY clause
select * from
TableFetch1
order by col1
FETCH FIRST 2 ROWS ONLY;
select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
order by col1) - 1) / COUNT(*) OVER () < 20 / 100;
select * from
TableFetch1
order by col1 offset 2 rows FETCH FIRST 2 ROWS ONLY;
select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
order by col1) - 1 - 2) / COUNT(*) OVER () < 60 / 100
LIMIT NULL OFFSET 2;
select * from
TableFetch1
FETCH FIRST 1 ROWS ONLY;
select * from
TableFetch1
LIMIT NULL OFFSET 2;
In Oracle, the FETCH / OFFSET WITH TIES is ignored when no ORDER BY is specified in the SELECT. This case will be transformed to a FETCH / OFFSET with the ONLY keyword in Snowflake, please note that in Snowflake the ONLY keyword has no effect in the results and is used just for readability.
Pivot
Snowflake does not support the following statements:
- Rename columns
- Multiple Columns
Input Code:
IN -> Oracle_06.sql
select * from schema1.table1
PIVOT(count(*) as count1 FOR (column1, column2) IN (row1 as rowName));
Output Code:
OUT -> Oracle_06.sql
select * from
schema1.table1
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT RENAME COLUMN NOT SUPPORTED ***/!!!
PIVOT (count(*)
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT MULTIPLE COLUMN NOT SUPPORTED ***/!!!
FOR (column1, column2)
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT RENAME COLUMN NOT SUPPORTED ***/!!!
IN (row1 as rowName));
Unpivot
Snowflake does not support the following statements:
- INCLUDE / EXCLUDE NULLS
Input Code:
IN -> Oracle_07.sql
select * from schema1.table1
UNPIVOT INCLUDE NULLS (column1 FOR column2 IN (ANY, ANY));
Output Code:
OUT -> Oracle_07.sql
select * from
schema1.table1
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT INCLUDE NULLS NOT SUPPORTED ***/!!!
UNPIVOT ( column1 FOR column2 IN (
ANY,
ANY));
Transformation of JOIN (+) to ANSI Syntax
This translation is currently deactivated and it's only meant for reference for translations done with previous versions of SnowConvert. For the current translation check the section above.
SnowConvert translates the NON-ANSI special outer join (+) syntax to ANSI outer join syntax. This subsection shows some examples:
To LEFT OUTER JOIN
Example 1:
Input Code:
IN -> Oracle_08.sql
-- Additional Params: --OuterJoinsToOnlyAnsiSyntax
SELECT d.department_name,
e.employee_name
FROM departments d, employees e
WHERE d.department_id = e.department_id (+)
AND d.department_id >= 30;
Output Code:
OUT -> Oracle_08.sql
SELECT d.department_name,
e.employee_name
FROM
departments d
LEFT OUTER JOIN
employees e
ON d.department_id = e.department_id
WHERE
d.department_id >= 30;
Example 2:
Input Code:
IN -> Oracle_09.sql
-- Additional Params: --OuterJoinsToOnlyAnsiSyntax
SELECT d.department_name,
e.employee_name
FROM departments d, employees e
WHERE d.department_id(+) = e.department_id
AND d.department_id >= 30;
Output Code:
OUT -> Oracle_09.sql
SELECT d.department_name,
e.employee_name
FROM
employees e
LEFT OUTER JOIN
departments d
ON d.department_id = e.department_id
WHERE
d.department_id >= 30;
Example 3: Multiple join
Input Code:
IN -> Oracle_10.sql
-- Additional Params: --OuterJoinsToOnlyAnsiSyntax
SELECT d.department_name,
e.employee_name
FROM departments d, employees e, projects p
WHERE e.department_id(+) = d.department_id
AND p.department_id(+) = d.department_id
AND d.department_id >= 30;
Output Code:
OUT -> Oracle_10.sql
SELECT d.department_name,
e.employee_name
FROM
departments d
LEFT OUTER JOIN
employees e
ON e.department_id = d.department_id
LEFT OUTER JOIN
projects p
ON p.department_id = d.department_id
WHERE
d.department_id >= 30;
Example 4: Join with other kinds of conditional
Input Code:
IN -> Oracle_11.sql
-- Additional Params: --OuterJoinsToOnlyAnsiSyntax
SELECT d.department_name,
e.employee_name
FROM departments d, employees e
WHERE d.department_id(+) = e.department_id
AND d.location(+) IN ('CHICAGO', 'BOSTON', 'NEW YORK')
AND d.department_id >= 30;
Output Code:
OUT -> Oracle_11.sql
SELECT d.department_name,
e.employee_name
FROM
employees e
LEFT OUTER JOIN
departments d
ON d.department_id = e.department_id
AND d.location IN ('CHICAGO', 'BOSTON', 'NEW YORK')
WHERE
d.department_id >= 30;
Example 5: Join with (+) inside a function
Input Code:
IN -> Oracle_12.sql
-- Additional Params: --OuterJoinsToOnlyAnsiSyntax
SELECT d.department_name,
e.employee_name
FROM departments d, employees e
WHERE SUBSTR(d.department_name, 1, NVL(e.department_id, 1) ) = e.employee_name(+);
Output Code:
OUT -> Oracle_12.sql
SELECT d.department_name,
e.employee_name
FROM
departments d
LEFT OUTER JOIN
employees e
ON SUBSTR(d.department_name, 1, NVL(e.department_id, 1) ) = e.employee_name;
Please be aware that some of the patterns that were translated to LEFT OUTER JOIN could retrieve the rows in a different order.
To CROSS JOIN
Example 6: Complex case that requires the use of CROSS JOIN
Input Code:
IN -> Oracle_13.sql
-- Additional Params: --OuterJoinsToOnlyAnsiSyntax
SELECT d.department_name,
e.employee_name,
p.project_name,
c.course_name
FROM departments d, employees e, projects p, courses c
WHERE
e.salary (+) >= 2000 AND
d.department_id = e.department_id (+)
AND p.department_id = e.department_id(+)
AND c.course_id = e.department_id(+)
AND d.department_id >= 30;
Output Code:
OUT -> Oracle_13.sql
SELECT d.department_name,
e.employee_name,
p.project_name,
c.course_name
FROM
departments d
CROSS JOIN projects p
CROSS JOIN courses c
LEFT OUTER JOIN
employees e
ON
e.salary >= 2000
AND
d.department_id = e.department_id
AND p.department_id = e.department_id
AND c.course_id = e.department_id
WHERE
d.department_id >= 30;
Hierarchical Queries
Hierarchical queries in Snowflake allow to organize and retrieve data in a tree-like structure, typically using the CONNECT BY clause. This clause Joins a table to itself to process hierarchical data in the table.
Input Code:
IN -> Oracle_14.sql
SELECT employee_ID, manager_ID, title
FROM employees
START WITH manager_ID = 1
CONNECT BY manager_ID = PRIOR employee_id;
Output Code:
OUT -> Oracle_14.sql
SELECT employee_ID, manager_ID, title
FROM
employees
START WITH manager_ID = 1
CONNECT BY
manager_ID = PRIOR employee_id;
