MSCEWI4049

Multiple Line If Body translation planned to be delivered in the future.

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

Severity

Medium

Description

Translation for if statements that contain a Begin ... End block inside their body is not supported yet for the entire statement block. Instead, this EWI and a Select Null are added.

Code Example

Input Code:

CREATE OR ALTER FUNCTION [PURCHASING].[FOO](@status INT)
Returns INT
As
Begin
    declare @result as int = 10;
    SELECT @result = quantity FROM TABLE1 WHERE COL1 = @status;
    IF @result = 3
    BEGIN
        IF @result>0 SELECT @result=0  ELSE SELECT @result=1
        SELECT @result = 1 
    END
    return @result;
End

Output Code:

CREATE OR REPLACE FUNCTION PURCHASING.FOO (STATUS FLOAT)
Returns INT
LANGUAGE SQL
AS
$$
    WITH CTE1 AS
    (
        SELECT
            10 AS RESULT
    ),
    CTE2 AS
    (
        SELECT
            quantity AS RESULT
        FROM
            TESTS.TABLE1
        WHERE
            COL1 = STATUS
    ),
    CTE6 AS
    (
        SELECT
            CASE
                WHEN (
                    SELECT
                        RESULT
                    FROM
                        CTE2
                ) = 3
                    THEN (SELECT
                        /*** MSC-ERROR - MSCEWI4049 - TRANSLATION FOR MULTIPLE LINE IF BODY IS PLANNED TO BE DELIVERED IN THE FUTURE ***/
                        /*SELECT
                           @result = 1*/
                        null
                    FROM
                        (
                            SELECT
                                /*** MSC-ERROR - MSCEWI4049 - TRANSLATION FOR MULTIPLE LINE IF BODY IS PLANNED TO BE DELIVERED IN THE FUTURE ***/
                                /*IF @result > 0
                                   SELECT
                                      @result = 0
                                ELSE
                                   SELECT
                                      @result = 1*/
                                null
                        ) T1)
            END AS RESULT
    )
    SELECT
        RESULT
    FROM
        CTE6
$$;

Manual Support

Case 1: Single Statement

For these cases, the transformation would be straightforward, since the transformed statement would appear in a select clause

IF @result = 0
BEGIN 
    SET @result =1
END

CASE WHEN (SELECT RESULT FROM CTE2)= 0 THEN
( SELECT 1 AS RESULT )

Case 2: Multiple Statements

For cases in which multiple statements are being transformed, we should transform the N Statement, and use it as the source table for the N+1 Statement.

IF @result = 0
BEGIN 
    Statement1
    Statement2
    Statement3
END

CASE WHEN (SELECT RESULT FROM CTE2)= 0 THEN
(
    SELECT TransformedStatement3
    FROM (
        SELECT TransformedStatement2
        FROM (
            SELECT TransformedStatement1
        ) T1
    ) T2
)

Case 3: Multiple set statements

For these cases, it will be necessary to replicate a transformation for each set statement.

IF @result = 0
BEGIN 
    SET @var1 = 1
    SET @var2 = 3
    SET @var3 = @var2
END

WITH CTE1 AS (
    SELECT 
        CASE WHEN (SELECT
                        RESULT
                    FROM
                        CTE0) = 0 THEN
        (SELECT 1) AS VAR1)
WITH CTE2 AS (
    SELECT
        CASE WHEN (SELECT
                        RESULT
                    FROM
                        CTE0)= 0 THEN
        (SELECT 3) AS VAR2)
WITH CTE3 AS (
    SELECT
        CASE WHEN (SELECT
                        RESULT
                    FROM
                        CTE0)= 0 THEN
        (SELECT T1.VAR2 
        FROM ((SELECT 3) AS VAR2) AS T1) AS VAR3) 
...

Recommendations

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