Functional Equivalence Helpers

A list of helpers functions in JavaScript that procedures in SnowFlake can use, in order to better support several Teradata language features.

Depending on what is in each Stored Procedure in Teradata, SnowConvert will create one or more of the following javascript functions inside them.

CompareDates

A function that compares dates handling nullity. In Javascript, it is needed to call .getTime() for date comparisons.

var CompareDates = function(value1, value2) {
	var value1Time = value1 && value1.getTime() || null;
	var value2Time = value2 && value2.getTime() || null;
	if (value1Time == null && value2Time == null) return null; /*in SQL null == null is equal to null as well as any other comparison */
	return value1Time > value2Time? 1 : value1Time<value2Time? -1 : 0;
}

BetweenFunc

A function to handle the BETWEEN statement in Teradata.

var BetweenFunc = function (expression,startExpr,endExpr) {
	if ([expression,startExpr,endExpr].some((arg) => arg == null)) {
		  return false;
	}
	return expression >= startExpr && expression <= endExpr;
};

LikeFunction()

A function to handle the LIKE statement in Teradata.

var likeFunction = function (leftExpr,rightExpr) {
	RegExp.escape = function (text) {
		if (!arguments.callee.sRE) {
			var specials = ['/','.','*','+','?','|','(',')','[',']','{','}','\\'];
			arguments.callee.sRE = new RegExp('(\\' + specials.join('|\\') + ')','g');
		}
		return text.replace(arguments.callee.sRE,'\\$1');
	}
	var likeExpr = RegExp.escape(rightExpr);
	var likeResult = new RegExp(likeExpr.replace('%','.*').replace('_','.')).exec(leftExpr) != null;
	return likeResult;
};

ERROR_HANDLERS()

The main error-handling routine.

var continue_handler_1 = function (error) {
   {
	  V_SQL_VALUE = SQLSTATE;
	  V_EXCEPTION_FLAG = `Y`;
   }
};

// Main error-handling routine
var ERROR_HANDLERS = function (error) {
   switch(error.state) {
	  //Conversion Warning - handlers for the switch default (SQLWARNING/SQLEXCEPTION/NOT FOUND) can be the following
	  default:continue_handler_1(error);
   }
};

INSERT_TEMP

This helper has been deprecated in stored procedures since version 2.0.15.

A function to create a temporary table using the argument query with the given parameters.

var procname = `PUBLIC.Procedure1`;
var temptable_prefix, tablelist = [];
var INSERT_TEMP = function (query,parameters) {
		if (!temptable_prefix) {
	  		var sql_stmt = `select current_session() || '_' || to_varchar(current_timestamp, 'yyyymmddhh24missss')`;
	      var rs = snowflake.createStatement({
	         sqlText : sql_stmt,
	         binds : []
	      }).execute();
	      temptable_prefix = rs.next() && (procname + '_TEMP_' + rs.getColumnValue(1) + '_');
	  }
	  var tablename = temptable_prefix + tablelist.length;
	  tablelist.push(tablename);
	  var sql_stmt = `CREATE OR REPLACE TEMPORARY TABLE ${tablename} AS ${query}`;
	  snowflake.execute({
	  		sqlText : sql_stmt,
	      binds : parameters
	  });
	  return tablename;
};

IS_NOT_FOUND()

A function that validates when a SELECT returns no values or a sentence affects zero rows. This is done in order to emulate the same behavior as Teradata, when there are exits or continue handlers for NOT FOUND EXCEPTIONS.

let IS_NOT_FOUND = (stmt) => {
	   let n = -1;
	   let cmd = stmt.getSqlText().replace(new RegExp("\\/\\*.*\\*\\/","gsi"),"").replace(new RegExp("--.*?\\n","gsi"),"");
	   let matched = cmd.match(new RegExp("\\s*(\\w+)\\s+"),"");
	   if (matched) {
	      cmd = matched[1].toUpperCase();
	      switch(cmd) {
		       case "CALL":
	         case "DROP":
	         case "CREATE":
	         case "ALTER":
	         case "SELECT":
								n = stmt.getRowCount();
	         break;
	         default:n = stmt.getNumRowsAffected();
	         break;
	      }
	   }
	   return n == 0;
	};

HANDLE_NOTFOUND()

This function uses the above IS_NOT_FOUND function to validate when an artificial error 'NOT FOUND' is being thrown.

 	let HANDLE_NOTFOUND = (stmt) => {
	   if (IS_NOT_FOUND(stmt) && (error = new Error('NOT_FOUND')) && (NOT_FOUND = true) && ([error.code,error.state] = ['020000','020000'])) throw error;
	};

PROCRESULTS()

A function that takes zero or multiple output parameters and binds them with the _OUTQUERIES in an array in order to be returned.

let PROCRESULTS = (...OUTPARAMS) => JSON.stringify([...OUTPARAMS,[..._OUTQUERIES]]);