This is a deprecated version of the SnowConvert documentation, please visit the official site .
Severity
Low
Description
In Oracle, using WITH TIES without ORDER BY will make the query not return additional rows. The behavior would be the same as using ONLY instead of WITH TIES so the code is transformed to the equivalent that uses the ONLY keyword.
Example Code
Input Code:
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;
Output Code:
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;
Recommendations
Make sure the query has an ORDER BY clause before migrating the code.
