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

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;

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

Last updated

Was this helpful?