MSCEWI4020

Common Table Expression not supported

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

Severity

Low

Description

This EWI is added when SnowConvert replace a Common Table Expression with TABLE(RESULT_SCAN(LAST_QUERY_ID())) in order to preserve the original behavior of the statement. The CTE is removed and the query is executed as a normal statatement.

Code Example

Input Code:

WITH CTE AS( SELECT * from table1)
INSERT INTO Table2 (a,b,c,d)
SELECT a,b,c,d
FROM CTE
WHERE e IS NOT NULL;

With DIFilter as (select * from t1)
    UPDATE t   
    SET     
    [PD_FIN] = s.[FIN]    
    ,[PD_Name]=s.[patient_name]      
    ,[BatchId] = s.[BatchId]     
    ,[LastUpdated] = getdate()   
    from  DIFilter as s;

Output Code:

/*** MSC-WARNING - MSCEWI4020 - COMMON TABLE EXPRESSION IS NOT SUPPORTED FOR INSERT STATEMENT. TABLE(RESULT_SCAN(LAST_QUERY_ID())) WAS USED INSTEAD IN ORDER TO PRESERVE THE CURRENT BEHAVOUR ***/
SELECT * from PUBLIC.table1;

INSERT INTO PUBLIC.Table2 (a, b, c, d)
SELECT a,
b,
c,
d
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) AS CTE
WHERE e IS NOT NULL;

/*** MSC-WARNING - MSCEWI4019 - COMMON TABLE EXPRESSION IS NOT SUPPORTED FOR UPDATE STATEMENT. TABLE(RESULT_SCAN(LAST_QUERY_ID())) WAS USED INSTEAD IN ORDER TO PRESERVE THE CURRENT BEHAVOUR ***/
select * from PUBLIC.t1;
    UPDATE PUBLIC.t
    SET PD_FIN = s.FIN
    , PD_Name = s.patient_name
    , BatchId = s.BatchId
    , LastUpdated = CURRENT_DATE() /*** MSC-WARNING - MSCEWI1046 - 'getdate' FUNCTION MAPPED TO 'CURRENT_DATE', FUNCTIONAL EQUIVALENCE VERIFICATION PENDING ***/
    from TABLE(RESULT_SCAN(LAST_QUERY_ID())) AS s ;

Recommendations

• No additional user actions are required.

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