Select

In this section you could find information about the select query syntax and its convertions.

Overall Select Translation

Simple select

-- Oracle
select * from table1;
select col1 from schema1.table1;

-- Snowflake
select * from PUBLIC.table1;
select col1 from schema1.table1;

Where clause

-- Oracle
select col1 from schema1.table1 WHERE col1 = 1 and id > 0 or id < 1;

-- Snowflake
select col1 from schema1.table1 WHERE col1 = 1 and id > 0 or id < 1;

Order By clause

-- Oracle
select col1 from schema1.table1 order by id ASC;

-- Snowflake
select col1 from schema1.table1 order by id ASC;

Group by

-- Oracle
select col1 from schema1.table1 GROUP BY id;

-- Snowflake
select col1 from schema1.table1 GROUP BY id;

Model Clause

The model clause is not supported yet.

Row Limiting Clause

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


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;

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;

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;

-- Snowflake
select * from PUBLIC.TableFetch1
FETCH FIRST 2 ROWS ONLY;
select * from PUBLIC.TableFetch1
QUALIFY (ROW_NUMBER() OVER ( ORDER BY NULL) - 1) / COUNT(*) OVER () < 20 / 100;
select * from PUBLIC.TableFetch1
QUALIFY RANK() OVER ( order by col1) <= 2;
select * from PUBLIC.TableFetch1
QUALIFY (RANK() OVER ( order by col1) - 1) / COUNT(*) OVER () < 20 / 100;

select * from PUBLIC.TableFetch1 offset 2 rows FETCH FIRST 2 ROWS ONLY;
select * from PUBLIC.TableFetch1
QUALIFY (ROW_NUMBER() OVER ( ORDER BY NULL) - 1 - 2) / COUNT(*) OVER () < 60 / 100
LIMIT NULL OFFSET 2;
select * from PUBLIC.TableFetch1
QUALIFY RANK() OVER ( order by col1) - 2 <= 2
LIMIT NULL OFFSET 2;
select * from PUBLIC.TableFetch1
QUALIFY (RANK() OVER ( order by col1) - 1 - 2) / COUNT(*) OVER () < 60 / 100
LIMIT NULL OFFSET 2;

select * from PUBLIC.TableFetch1
/*** MSC-WARNING - MSCEWI3063 - QUERY THAT HAS 'WITH TIES' CLAUSE WITHOUT 'ORDER BY' WAS TRANSFORMED TO 'ONLY' VARIANT ***/
FETCH FIRST 2 ROWS ONLY;
select * from PUBLIC.TableFetch1
/*** MSC-WARNING - MSCEWI3063 - QUERY THAT HAS 'WITH TIES' CLAUSE WITHOUT 'ORDER BY' WAS TRANSFORMED TO 'ONLY' VARIANT ***/
QUALIFY (ROW_NUMBER() OVER ( ORDER BY NULL) - 1) / COUNT(*) OVER () < 20 / 100;
select * from PUBLIC.TableFetch1
 /*** MSC-WARNING - MSCEWI3063 - QUERY THAT HAS 'WITH TIES' CLAUSE WITHOUT 'ORDER BY' WAS TRANSFORMED TO 'ONLY' VARIANT ***/
 offset 2 rows FETCH NEXT 2 ROWS ONLY;
select * from PUBLIC.TableFetch1
/*** MSC-WARNING - MSCEWI3063 - QUERY THAT HAS 'WITH TIES' CLAUSE WITHOUT 'ORDER BY' WAS TRANSFORMED TO 'ONLY' VARIANT ***/
QUALIFY (ROW_NUMBER() OVER ( ORDER BY NULL) - 1 - 2) / COUNT(*) OVER () < 60 / 100
 LIMIT NULL OFFSET 2;

select * from PUBLIC.TableFetch1 order by col1
FETCH FIRST 2 ROWS ONLY;
select * from PUBLIC.TableFetch1
QUALIFY (ROW_NUMBER() OVER ( order by col1) - 1) / COUNT(*) OVER () < 20 / 100;
select * from PUBLIC.TableFetch1 order by col1 offset 2 rows FETCH FIRST 2 ROWS ONLY;
select * from PUBLIC.TableFetch1
QUALIFY (ROW_NUMBER() OVER ( order by col1) - 1 - 2) / COUNT(*) OVER () < 60 / 100
LIMIT NULL OFFSET 2;

select * from PUBLIC.TableFetch1
FETCH FIRST 1 ROWS ONLY;

select * from PUBLIC.TableFetch1
LIMIT NULL OFFSET 2;

Pivot

Snowflake doesn`t support the following statements: - Rename columns - Multiple Columns

-- Oracle
select * from schema1.table1
PIVOT(count(*) as count1 FOR (column1, column2) IN (row1 as rowName));
-- Snowflake
select * from schema1.table1
-- ** MSC-ERROR - MSCEWI3034 - PIVOT/UNPIVOT RENAME COLUMN NOT SUPPORTED **
-- ** MSC-ERROR - MSCEWI3034 - PIVOT/UNPIVOT MULTIPLE COLUMN NOT SUPPORTED **
-- ** MSC-ERROR - MSCEWI3034 - PIVOT/UNPIVOT RENAME COLUMN NOT SUPPORTED **
--PIVOT (count(*) FOR (column1, column2) IN ( row1))
                                                  ;

Unpivot

Snowflake doesn`t support the following statements: - INCLUDE / EXCLUDE NULLS

-- Oracle
select * from schema1.table1 
UNPIVOT INCLUDE NULLS (column1 FOR column2 IN (ANY, ANY)); 
-- Snowflake
select * from schema1.table1
/*** MSC-ERROR - MSCEWI3034 - 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:

-- Oracle
SELECT d.department_name,
       e.employee_name      
FROM   departments d, employees e
WHERE  d.department_id = e.department_id (+) 
AND    d.department_id >= 30;

-- Expected Snowflake Code
SELECT d.department_name,
       e.employee_name
/**** WARNING: NON-ANSI OUTER JOIN SYNTAX CONVERTED TO ANSI SYNTAX ****/     
FROM   departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
WHERE  d.department_id >= 30;

Example 2:

-- Oracle
SELECT d.department_name,
       e.employee_name      
FROM   departments d, employees e
WHERE  d.department_id(+)  = e.department_id 
AND    d.department_id >= 30;

-- Expected Snowflake Code
SELECT d.department_name,
       e.employee_name
/**** WARNING: NON-ANSI OUTER JOIN SYNTAX CONVERTED TO ANSI SYNTAX ****/
FROM employees e 
LEFT OUTER JOIN departments d ON  d.department_id  = e.department_id
WHERE    d.department_id >= 30;

Example 3: Multiple join

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

-- Snowflake 
SELECT d.department_name,
       e.employee_name  
/**** WARNING: NON-ANSI OUTER JOIN SYNTAX CONVERTED TO ANSI SYNTAX ****/
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

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

-- Expected Snowflake Code
SELECT d.department_name,
       e.employee_name
/**** WARNING: NON-ANSI OUTER JOIN SYNTAX CONVERTED TO ANSI SYNTAX ****/
FROM employees e 
LEFT OUTER JOIN departments d ON  d.department_id  = e.department_id
WHERE    d.location IN ('CHICAGO', 'BOSTON', 'NEW YORK')
AND    d.department_id >= 30;

Example 5: Join with (+) inside a function

-- Oracle
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(+);

-- Expected Snowflake Code
SELECT d.department_name,
       e.employee_name
/**** WARNING: NON-ANSI OUTER JOIN SYNTAX CONVERTED TO ANSI SYNTAX ****/
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

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

-- Expected Snowflake Code 
SELECT d.department_name,
        e.employee_name,
        p.project_name,
        c.course_name
 /**** WARNING: NON-ANSI OUTER JOIN SYNTAX CONVERTED TO ANSI SYNTAX ****/
 FROM departments d 
 CROSS JOIN projects p 
 CROSS JOIN courses c 
 LEFT OUTER JOIN employees e ON 
 d.department_id = e.department_id AND 
 e.department_id = p.department_id AND 
 e.salary >= 2000 AND 
 c.course_id  = e.department_id
 WHERE
 d.department_id >= 30;

Select Flashback Query

Select Flashback Query