SSC-EWI-OR0071

Set Quantifier Not Supported

Severity

Low

Description

Quantifier 'all' is not supported in Snowflake. The modifier is removed from the source code, and a warning is added; the resulting code may behave unexpectedly.

Example Code

Input Code:

IN -> Oracle_01.sql

SELECT location_id  FROM locations 
MINUS ALL 
SELECT location_id  FROM departments;

Output Code:

OUT -> Oracle_01.sql

SELECT location_id  FROM
locations
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0071 - QUANTIFIER 'ALL' NOT SUPPORTED FOR THIS SET OPERATOR, RESULTS MAY DIFFER ***/!!!
MINUS
SELECT location_id  FROM
departments;

In Snowflake, the INTERSECT and MINUS/EXCEPT operators will always remove duplicate values.

Recommendations

• Check alternatives in Snowflake to emulate the functionality of the "all" quantifier. Below is a workaround for MINUS ALL and EXCEPT ALL.

SELECT location_id FROM
(
    SELECT location_id, ROW_NUMBER()OVER(PARTITION BY location_id ORDER BY 1) rn
    FROM locations
    MINUS
    SELECT number_val, ROW_NUMBER()OVER(PARTITION BY location_id ORDER BY 1) rn
    FROM departments
);

• If you need more support, you can email us at [e-mail]