MSCEWI3063

A query that has the 'WITH TIES' clause without 'ORDER BY' was transformed to the 'ONLY' variant.

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

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.

• If you need more support, you can email us at [email protected]