Exists

Transact-SQL subqueries using EXISTS statement transformation details

Types of Subqueries

Subqueries can be categorized as correlated or uncorrelated:

A correlated subquery, refers to one or more columns from outside of the subquery. (The columns are typically referenced inside the WHERE clause of the subquery.) A correlated subquery can be thought of as a filter on the table that it refers to, as if the subquery were evaluated on each row of the table in the outer query.

An uncorrelated subquery, has no such external column references. It is an independent query, the results of which are returned to and used by the outer query once (not per row).

The EXISTS statement is considered a correlated subquery.

Source Code

CREATE PROCEDURE ProcExists
AS
BEGIN
IF(EXISTS(Select AValue from ATable))
  return 1;
END;

Expected Code

CREATE OR REPLACE PROCEDURE ProcExists ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   var fetch = (count,rows,stmt) => (count && rows.next() && Array.apply(null,Array(stmt.getColumnCount())).map((_,i) => rows.getColumnValue(i + 1))) || [];
   var SELECT = (sql,binds = [],...args) => {
      var reducers = args.filter((i) => i instanceof Function);
      reducers = reducers.length ? reducers : [(value) => value]
      args = args.splice(0,args.length - reducers.length)
      EXEC("SELECT " + sql,binds)
      if (ROW_COUNT < 1) return;
      var colCount = _ROWS.getColumnCount();
      if (colCount != reducers.length) throw new Exception("Missing arguments results has ${colCount} columns");
      var cols = Array.from(Array(colCount),() => []);
      while ( _ROWS.next() ) {
         for(var i = 0;i < colCount;i++) {
            cols[i].push(_ROWS.getColumnValue(i + 1))
         }
      }
      if (colCount == 1) {
         cols[0].forEach((value) => reducers[0](value))
         return cols[0];
      }
      for(var i = 0;i < colCount;i++) {
         cols[i].forEach((value) => reducers[i](value))
      }
   };
   var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', ERROR_HANDLERS, NUM_ROWS_AFFECTED, PROC_NAME = arguments.callee.name;
   var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
   var fixBind = function (arg) {
      arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;
      return arg;
   };
   var EXEC = (stmt,binds = [],noCatch = false) => {
      binds = binds ? binds.map(fixBind) : binds;
      for(var stmt of stmt.split(";").filter((_) => _)) {
         try {
            _RS = snowflake.createStatement({
                  sqlText : stmt,
                  binds : binds
               });
            _ROWS = _RS.execute();
            ROW_COUNT = _RS.getRowCount();
            NUM_ROWS_AFFECTED = _RS.getNumRowsAffected();
            return {
               THEN : (action) => !SQLCODE && action(fetch(_ROWS))
            };
         } catch(error) {
            let rStack = new RegExp('At .*, line (\\d+) position (\\d+)');
            let stackLine = error.stackTraceTxt.match(rStack) || [0,-1];
            MESSAGE_TEXT = error.message.toString();
            SQLCODE = error.code.toString();
            SQLSTATE = error.state.toString();
            snowflake.execute({
               sqlText : `SELECT UPDATE_ERROR_VARS_UDF(?,?,?,?,?)`,
               binds : [stackLine[1],SQLCODE,SQLSTATE,MESSAGE_TEXT,PROC_NAME]
            });
            throw error;
         }
      }
   };
   // END REGION

   if (SELECT(`EXISTS(Select AValue from PUBLIC.ATable)`)) {
      return 1;
   }
$$;