MSCEWI5006

INTERMEDIATE RESULT TABLE IS NOT SUPPORTED

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

Severity

Medium

Description

This message is shown when a DATA CHANGE TABLE REFERENCE is found in a FROM Clause. A DATA CHANGE TABLE REFERENCE specifies an intermediate table, which consists of the rows that are changed by an UPDATE, DELETE or INSERT statement included in the DATA CHANGE TABLE REFERENCE.

In Snowflake, this is not supported, since it can't modify the rows and return a result set of table at the same time, hence the Select is commented.

Code example

DB2 Input code:

Select statement

Select * from OLD Table(UPDATE T1 SET NAME = 'Tony' where ID = 4);

Update statement

UPDATE (SELECT EMPNO, SALARY, COMM,
     AVG(SALARY) OVER (PARTITION BY WORKDEPT),
     AVG(COMM) OVER (PARTITION BY WORKDEPT)
     FROM EMPLOYEE E) AS E(EMPNO, SALARY, COMM, AVGSAL, AVGCOMM)
   SET (SALARY, COMM) = (AVGSAL, AVGCOMM)
   WHERE EMPNO = '000120';
   
 UPDATE TABLE6 INCLUDE (col1, col2)
	SET Column1 = 1;

Snowflake Output code:

Select statement

-- ** MSC-ERROR - MSCEWI5006 - INTERMEDIATE RESULT TABLE IS NOT SUPPORTED. **
--Select * from OLD Table(UPDATE T1 SET NAME = 'Tony' where ID = 4)

Update statement

-- ** MSC-ERROR - MSCEWI5010 - QUERY AS UPDATE TARGET NAME IS NOT SUPPORTED IN SNOWFLAKE **
--UPDATE (SELECT EMPNO, SALARY, COMM,
--     AVG(SALARY) OVER (PARTITION BY WORKDEPT),
--     AVG(COMM) OVER (PARTITION BY WORKDEPT)
--     FROM EMPLOYEE E) AS E(EMPNO, SALARY, COMM, AVGSAL, AVGCOMM)
--   SET (SALARY, COMM) = (AVGSAL, AVGCOMM)
--   WHERE EMPNO = '000120'

-- ** MSC-ERROR - MSCEWI5006 - INTERMEDIATE RESULT TABLE IS NOT SUPPORTED. **
--UPDATE TABLE6 INCLUDE (col1, col2)
--	SET Column1 = 1

Recommendations

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