SSC-FDM-0002

Correlated Subquery May Have Functional Differences

Description

This message is reported when a Correlated Subquery (subquery that refers to a column from the outer query) is located. This type of subqueries can, in some cases, present some functional differences in Snowflake (Working with Subqueries).

Code Example

Input Code:

IN -> SqlServer_01.sql

CREATE TABLE schema1.table1(column1 NVARCHAR(50), column2 NVARCHAR(50));
CREATE TABLE schemaA.tableA(columnA NVARCHAR(50), columnB NVARCHAR(50));

--Correlated Subquery
SELECT columnA FROM schemaA.tableA ta WHERE columnA = (SELECT SUM(column1) FROM schema1.table1 t1 WHERE t1.column1 = ta.columnA);

Output Code:

OUT -> SqlServer_01.sql

CREATE OR REPLACE TABLE schema1.table1 (
column1 VARCHAR(50),
column2 VARCHAR(50))
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/11/2024",  "domain": "test" }}'
;

CREATE OR REPLACE TABLE schemaA.tableA (
columnA VARCHAR(50),
columnB VARCHAR(50))
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/11/2024",  "domain": "test" }}'
;

--Correlated Subquery
SELECT
columnA
FROM
schemaA.tableA ta
WHERE
columnA =
          --** SSC-FDM-0002 - CORRELATED SUBQUERIES MAY HAVE SOME FUNCTIONAL DIFFERENCES. **
          (SELECT
          SUM(column1) FROM
          schema1.table1 t1
          WHERE
          t1.column1 = ta.columnA
          );

Recommendations

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