Expressions and operators

Expressions

Concatenation Operator

You might also be interested in Concat helper.

Oracle concatenation is achieved in JavaScript using Template literal. Also it uses the Concat Helper to properly handle concatenations with nulls.

Oracle

CREATE OR REPLACE PROCEDURE CONCAT_TEST
IS
NUM1 INTEGER := 123;
NUM2 INTEGER := 321;
VAR1 VARCHAR(10) := 'value';
concat_var VARCHAR(100);
sql_stmt VARCHAR(100);
BEGIN
    concat_var := NUM1 || NUM2 || VAR1 || 'literal';   
    sql_stmt := 'INSERT INTO t1 VALUES (''' || concat_var || ''')';    
    EXECUTE IMMEDIATE sql_stmt;
END;

Snowflake

SnowConvert helpers Code removed from the example. You can find them here.

CREATE OR REPLACE PROCEDURE CONCAT_TEST ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code   
   let NUM1 = 123;
   let NUM2 = 321;
   let VAR1 = `value`;
   let CONCAT_VAR;
   let SQL_STMT;
   // ** MSC-WARNING - MSCEWI1038 - THIS STATEMENT MAY BE A DYNAMIC SQL THAT COULD NOT BE RECOGNIZED AND CONVERTED **
   CONCAT_VAR = `${concatValue(NUM1)}${concatValue(NUM2)}${concatValue(VAR1)}literal`;
   SQL_STMT = `INSERT INTO PUBLIC.t1 VALUES ('${concatValue(CONCAT_VAR)}')`;
   EXEC(SQL_STMT);
$$;

Logical Operators

Oracle

CREATE OR REPLACE PROCEDURE BOOLEAN_PROC (b_name VARCHAR2, b_value  BOOLEAN)
IS
BOOL1 BOOLEAN := FALSE;
x NUMBER := 5;
y NUMBER := NULL;
BEGIN

  IF b_value IS NULL THEN
    null;
  ELSIF b_value = TRUE THEN
    null;
  ELSIF b_value = TRUE AND b_value = BOOL1  OR b_value = BOOL1 THEN
    null;
  ELSIF x > y THEN
    null;
  ELSIF x != y AND x <> y THEN
    null;
  ELSE
    null;
  END IF;
END;

Snowflake

You might also be interested in IS NULL helper.

SnowConvert helpers Code removed from the example. You can find them here.

CREATE OR REPLACE PROCEDURE BOOLEAN_PROC (b_name STRING, b_value  BOOLEAN)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   let BOOL1 = false;
   let X = 5;
   let Y = undefined;
   if (IS_NULL(B_VALUE)) {
      null;
   } else if (B_VALUE == true) {
      null;
   } else if (B_VALUE == true && B_VALUE == BOOL1 || B_VALUE == BOOL1) {
      null;
   } else if (X > Y) {
      null;
   } else if (X != Y && X != Y) {
      null;
   } else {
      null;
   }
$$;

Comparison Operator

Documentation in progress.

IS [NOT] NULL

You might also be interested in IS NULL helper.

Oracle

CREATE OR REPLACE PROCEDURE NULL_TEST
IS
NUM1 INTEGER := 789;
BEGIN
    IF NUM1 IS NOT NULL THEN
        NULL;
    END IF;
    
    NUM1 := NULL;
    
    IF NUM1 IS NULL THEN
        NULL;
    END IF;
END;

Snowflake

SnowConvert helpers Code removed from the example. You can find them here.

CREATE OR REPLACE PROCEDURE NULL_TEST ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   let NUM1 = 789;
   if (!IS_NULL(NUM1)) {
      null;
   }
   NUM1 = undefined;
   if (IS_NULL(NUM1)) {
      null;
   }
$$;

Like Operator

You might also be interested in Like operator helper.

When there is a LIKE operation, the helper function will be called instead.

Oracle

CREATE OR REPLACE PROCEDURE PROCEDURE_WITH_LIKE AS
BEGIN
	IF 'ABC' LIKE '%A%' THEN
		 null;
	END IF;
  IF 'ABC' LIKE 'A%' THEN
     null;
  END IF;
  IF 'ABC' NOT LIKE 'D_%' THEN
     null;
  END IF;
  IF 'ABC' NOT LIKE 'D/%%' ESCAPE '/' THEN
     null;
  END IF;
END;

Snowflake

SnowConvert helpers Code removed from the example. You can find them here.

CREATE OR REPLACE PROCEDURE PROCEDURE_WITH_LIKE ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
  // REGION SnowConvert Helpers Code  
  if (LIKE(`ABC`,`%A%`)) {
      null;
   }
   if (LIKE(`ABC`,`A%`)) {
      null;
   }
   if (!LIKE(`ABC`,`D_%`)) {
      null;
   }
   if (!LIKE(`ABC`,`D/%%`,`/`)) {
      null;
   }
$$;

Between Operator

You may also be interested in Between operator helper.

Oracle

CREATE OR REPLACE PROCEDURE BETWEEN_TEST
IS
NUM1 INTEGER := 789;
US INTEGER := 1000;
BEGIN
    IF 800 BETWEEN US AND NUM1 THEN
        NULL;
    END IF;
    IF 'BA' BETWEEN 'B' AND 'CA' THEN
        NULL;
    END IF;
    
    -- Assign null to the variable num1
    NUM1 := NULL;
    
    IF (0 BETWEEN NULL AND NUM1) IS NULL THEN
        NULL;
    END IF;
END;

Snowflake

SnowConvert helpers Code removed from the example. You can find them here.

CREATE OR REPLACE PROCEDURE BETWEEN_TEST ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   let NUM1 = 789;
   let US = 1000;
   if (BetweenFunc(800,US,NUM1)) {
      null;
   }
   if (BetweenFunc(`BA`,`B`,`CA`)) {
      null;
   }
   // Assign null to the variable num1
   NUM1 = undefined;
   if (IS_NULL(BetweenFunc(0,undefined,NUM1))) {
      null;
   }
$$;

IN Operator

Oracle

CREATE OR REPLACE PROCEDURE IN_PROC
IS
letter VARCHAR2(1) := 'm';
BEGIN
  IF letter IN ('a', 'b', 'c') THEN
    null;  
  ELSE
    null;
  END IF;
END;

Snowflake

SnowConvert helpers Code removed from the example. You can find them here.

CREATE OR REPLACE PROCEDURE IN_PROC ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   let LETTER = `m`;
   if ([`a`,`b`,`c`].includes(LETTER)) {
      null;
   } else {
      null;
   }
$$;

Boolean Expressions

Oracle

CREATE OR REPLACE PROCEDURE BOOLEAN_TEST
IS
done BOOLEAN;
BEGIN
  -- These WHILE loops are equivalent  
  done := FALSE;
  WHILE done = FALSE
    LOOP
      done := TRUE;
    END LOOP;    
 
  done := FALSE;
  WHILE NOT (done = TRUE)
    LOOP
      done := TRUE;
    END LOOP; 

  done := FALSE;
  WHILE NOT done
    LOOP
      done := TRUE;
    END LOOP;
END;

Snowflake

SnowConvert helpers Code removed from the example. You can find them here.

CREATE OR REPLACE PROCEDURE BOOLEAN_TEST ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   
   let DONE;
   // These WHILE loops are equivalent  
   DONE = false;
   while ( DONE == false ) {
      DONE = true;
   }
   DONE = false;
   while ( !(DONE == true) ) {
      DONE = true;
   }
   DONE = false;
   while ( !DONE ) {
      DONE = true;
   }
$$;

Function Expressions

For Function Expressions inside procedures, they are being converted to the corresponding function or expression in Snowflake. These function calls are passed to an EXEC with a CALL or a SELECT depending on the converted value.

Oracle

CREATE OR REPLACE PROCEDURE FUNCTIONS_TEST(DATEPARAM DATE)
IS
	STRING_VALUE VARCHAR(20) := 'HELLO';
BEGIN
	STRING_VALUE := TO_CHAR(123);
	STRING_VALUE := TO_CHAR(DATEPARAM, 'dd-mm-yyyy', 'NLS_DATE_LANGUAGE = language');
END;

Snowflake

SnowConvert helpers Code removed from the example. You can find them here.

CREATE OR REPLACE PROCEDURE FUNCTIONS_TEST (DATEPARAM DATE)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   let STRING_VALUE = `HELLO`;
   STRING_VALUE = (EXEC(`SELECT TO_CHAR(123)`))[0];
   STRING_VALUE = (EXEC(`SELECT
      /*** MSC-ERROR - MSCEWI3013 - NLS PARAMETER 'NLS_DATE_LANGUAGE = language' NOT SUPPORTED ***/
      TO_CHAR(/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'JSDATE_UDF' INSERTED. ***/
         PUBLIC.JSDATE_UDF(?), 'dd-mm-yyyy')`,[DATEPARAM]))[0];
$$;

For more information on the functions transformations check here.