Multiples IFs statement

This pattern defines a function that uses conditional statements over local variables.

Components:

Function Declaration:
- CREATE FUNCTION functionName(parameters) RETURN returnType
- Declares the function with input parameters and the return type.
Variable Declaration:
- Declares variables, including the return variable.
BEGIN-END Block with IF Statement:
- Check if a condition is true.
- Each case is used to assign a value over the same variable.

Conversion:

DECLARE SECTION : variables with default an expression are moved to a common table expression.

IF/ELSE statement can be handled using the CASE EXPRESSION inside the select allowing conditionals inside the queries.

RETURN statement is transformed to the final select.

IN -> Oracle_01.sql

CREATE OR REPLACE FUNCTION Case1 (
   in_date_ IN DATE,
   min_max_ IN VARCHAR2 )
RETURN DATE
IS
   cond_date_  DATE := CURRENT_DATE;
BEGIN
   IF ( in_date_ IS NULL ) THEN
      IF ( min_max_ = 'MIN' ) THEN
         cond_date_ := FOO1();
      ELSE
         cond_date_ := FOO2();
      END IF;
   ELSE
      cond_date_ := TRUNC(in_date_);
   END IF;
   RETURN cond_date_;
END Case1;

OUT -> Oracle_01.sql

CREATE OR REPLACE FUNCTION Case1 (in_date_ TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/, min_max_ VARCHAR)
RETURNS TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "09/06/2024" }}'
AS
$$
   WITH declaration_variables_cte0 AS
   (
      SELECT
         CURRENT_DATE AS
         cond_date_
   ),
   declaration_variables_cte1 AS
   (
      SELECT
         CASE
            WHEN ( in_date_ IS NULL )
               THEN CASE
                  WHEN ( min_max_ = 'MIN' )
                     THEN FOO1() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO1' NODE ***/!!!
                  ELSE FOO2() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO2' NODE ***/!!!
               END
            ELSE TRUNC(in_date_, 'DD')
         END AS cond_date_
      FROM
         declaration_variables_cte0
   )
   SELECT
      cond_date_
   FROM
      declaration_variables_cte1
$$;

IN -> Oracle_02.sql

CREATE OR REPLACE FUNCTION Case2 (
   year_        IN NUMBER,
   id           IN NUMBER) 
   RETURN VARCHAR2
IS
   base_value_        NUMBER;
   fully_depritiated_ VARCHAR2(5);
   residual_value_    NUMBER;
   acc_depr_prev_     NUMBER;
   acc_depr_          NUMBER;
BEGIN

   base_value_     := FOO1(year_, id);
   acc_depr_       := FOO2(year_, id);
   acc_depr_prev_  := FOO3(year_, id);
   residual_value_ := NVL(base_value_,0) -(acc_depr_ + acc_depr_prev_);

   IF (residual_value_=0 AND base_value_!=0) THEN
      fully_depritiated_ := 'TRUE';
   ELSE
      fully_depritiated_ := 'FALSE';
   END IF;

   RETURN fully_depritiated_;
END Case2;

OUT -> Oracle_02.sql

CREATE OR REPLACE FUNCTION Case2 (year_ NUMBER(38, 18), id NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "09/06/2024" }}'
AS
$$
   WITH declaration_variables_cte1 AS
   (
      SELECT
         FOO1(year_, id) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO1' NODE ***/!!! AS

         base_value_,
         FOO2(year_, id) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO2' NODE ***/!!! AS
         acc_depr_,
         FOO3(year_, id) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO3' NODE ***/!!! AS
         acc_depr_prev_,
         !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN ExactNumeric AND unknown ***/!!!
         NVL(base_value_,0) -(acc_depr_ + acc_depr_prev_) AS
         residual_value_,
         CASE
            WHEN (residual_value_=0 AND base_value_!=0)
               THEN 'TRUE'
            ELSE 'FALSE'
         END AS fully_depritiated_
   )
   SELECT
      fully_depritiated_
   FROM
      declaration_variables_cte1
$$;

IN -> Oracle_03.sql

CREATE OR REPLACE FUNCTION Case2_1 (
   year_        IN NUMBER,
   id           IN NUMBER) 
   RETURN VARCHAR2
IS
   base_value_        NUMBER;
   fully_depritiated_ VARCHAR2(5);
   residual_value_    NUMBER;
   acc_depr_prev_     NUMBER;
   acc_depr_          NUMBER;
BEGIN

   base_value_     := FOO1(year_, id);
   acc_depr_       := FOO2(year_, id);
   acc_depr_prev_  := FOO3(year_, id);
   residual_value_ := NVL(base_value_,0) -(acc_depr_ + acc_depr_prev_);

   IF (residual_value_=0 AND base_value_!=0) THEN
      fully_depritiated_ := 'TRUE';
   ELSE
      fully_depritiated_ := 'FALSE';
   END IF;

   fully_depritiated := fully_depritiated || ' CONCAT FOR TESTING';
   fully_depritiated := fully_depritiated || ' CONCAT FOR TESTING2';
   RETURN fully_depritiated_;
END Case2;

OUT -> Oracle_03.sql

CREATE OR REPLACE FUNCTION Case2_1 (year_ NUMBER(38, 18), id NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "09/06/2024" }}'
AS
$$
   WITH declaration_variables_cte1 AS
   (
      SELECT
         FOO1(year_, id) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO1' NODE ***/!!! AS

         base_value_,
         FOO2(year_, id) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO2' NODE ***/!!! AS
         acc_depr_,
         FOO3(year_, id) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO3' NODE ***/!!! AS
         acc_depr_prev_,
         !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN ExactNumeric AND unknown ***/!!!
         NVL(base_value_,0) -(acc_depr_ + acc_depr_prev_) AS
         residual_value_,
         CASE
            WHEN (residual_value_=0 AND base_value_!=0)
               THEN 'TRUE'
            ELSE 'FALSE'
         END AS fully_depritiated_,
         NVL(fully_depritiated :: STRING, '') || ' CONCAT FOR TESTING' AS

         fully_depritiated
   ),
   declaration_variables_cte2 AS
   (
      SELECT
         NVL(fully_depritiated :: STRING, '') || ' CONCAT FOR TESTING2' AS
         fully_depritiated,
         base_value_,
         acc_depr_,
         acc_depr_prev_,
         residual_value_
      FROM
         declaration_variables_cte1
   )
   SELECT
      fully_depritiated_
   FROM
      declaration_variables_cte2
$$;

IN -> Oracle_04.sql

CREATE OR REPLACE FUNCTION Case2_1 (
   year_        IN NUMBER,
   id           IN NUMBER) 
   RETURN VARCHAR2
IS
   base_value_        NUMBER;
   fully_depritiated_ VARCHAR2(5);
   residual_value_    NUMBER;
   acc_depr_prev_     NUMBER;
   acc_depr_          NUMBER;
BEGIN

   base_value_     := FOO1(year_, id);
   acc_depr_       := FOO2(year_, id);
   acc_depr_prev_  := FOO3(year_, id);
   residual_value_ := NVL(base_value_,0) -(acc_depr_ + acc_depr_prev_);

   IF (residual_value_=0 AND base_value_!=0) THEN
      fully_depritiated_ := 'TRUE';
   ELSE
      fully_depritiated_ := 'FALSE';
   END IF;

   fully_depritiated := fully_depritiated || ' CONCAT FOR TESTING';
   fully_depritiated := fully_depritiated || ' CONCAT FOR TESTING2';
   RETURN fully_depritiated_;
END Case2;

OUT -> Oracle_04.sql

CREATE OR REPLACE FUNCTION Case2_1 (year_ NUMBER(38, 18), id NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "09/06/2024" }}'
AS
$$
   WITH declaration_variables_cte1 AS
   (
      SELECT
         FOO1(year_, id) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO1' NODE ***/!!! AS

         base_value_,
         FOO2(year_, id) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO2' NODE ***/!!! AS
         acc_depr_,
         FOO3(year_, id) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO3' NODE ***/!!! AS
         acc_depr_prev_,
         !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN ExactNumeric AND unknown ***/!!!
         NVL(base_value_,0) -(acc_depr_ + acc_depr_prev_) AS
         residual_value_,
         CASE
            WHEN (residual_value_=0 AND base_value_!=0)
               THEN 'TRUE'
            ELSE 'FALSE'
         END AS fully_depritiated_,
         NVL(fully_depritiated :: STRING, '') || ' CONCAT FOR TESTING' AS

         fully_depritiated
   ),
   declaration_variables_cte2 AS
   (
      SELECT
         NVL(fully_depritiated :: STRING, '') || ' CONCAT FOR TESTING2' AS
         fully_depritiated,
         base_value_,
         acc_depr_,
         acc_depr_prev_,
         residual_value_
      FROM
         declaration_variables_cte1
   )
   SELECT
      fully_depritiated_
   FROM
      declaration_variables_cte2
$$;

Known Issues

No issues were found.

SSC-FDM-OR0042: Date Type Transformed To Timestamp Has A Different Behavior.
SSC-EWI-0073: Pending Functional Equivalence Review.
SSC-EWI-OR0036: Types resolution issues, the arithmetic operation may not behave correctly between string and date.

PreviousCursor with IF statement NextCURSOR

Last updated 2 months ago

Conversion:

Known Issues

Related EWIS