SqlSelect Function Javascript doesn't retrieve above 70000 rows. is that possible?

[Sinhue]

Hi everyone 

I have this javascript code after an enrichment activity:

// select rows of the last transition
var sql = "SELECT sVin,sMktSale,iCountVehicles,sDayDifference,tsDelivery,coalesce(tsDelivery - lag(tsDelivery) over(partition by sCustomerId order by tsDelivery), 0) as Difference FROM " + vars.tableName; 

var contracts = sqlSelect("resultQuery,@vin:string,@MktSale:string,@CountVehicles:int,@DayDifference:string,@deliveryDate:date,Difference:string", sql); // 

// Iterate each row

for each(var contract in contracts.resultQuery){

  var newString = contract.Difference;
  
  var sql = "UPDATE "+ vars.tableName + " SET sDayDifference='"+newString.toString()+"' WHERE sVin='"+contract.@vin.toString()+"'";
  //logInfo('Executing '+sql);
  sqlExec(sql);
}

the code works only if the enrichment activity has below 70000 rows but if the activity has above 70000 rows the contracts.resultQuery variable print undefined and the update statement doesn't execute.

Could you help me to understand what is the reason? is there a limit of rows to execute that command?

I really appreciate if you could help me.

Thanks.

SL.

[Manoj_Kumar_ · Accepted solution]

Helo @Sinhue ,

I know queryDef is limited to 10000 rows by default. Similarly, I believe sqlSelect is also limited to 70,000 so that you won't run out of memory.

Also, My suggestion would be to use enrichment instead of javascript because it would be more efficient and you won't get this error with enrichment.

Thanks.,

Manoj

[Sinhue]

Hi @Manoj_Kumar_ thanks for you answer, but how can I do that?, the purpose of my javascript code is to get the date difference between rows.

[Manoj_Kumar_]

You can use two query activites to replace your 2 sql queries and then use the enrichment to create a join on the basis of vin and in the output column of the enrichment you can add the difference and other columns

[gunnark17854189]

Sounds bad but use a Split - than the Script, than a Union we have the same Problem. The performance is very bad...

[AdrianESanchez]

As @Manoj_Kumar_ suggested, it is better to use OOTB activities as they allow us to perform a variety of things.

Nevertheless, sometimes you need to execute some JavaScript pieces of code with large sets.

For those, you may use pagination within the SQL.

Let me share a sample code below. That one select delivery Ids from the temporary worktable shared from the previous activity and saves the subject line on an additional column of that table.

NL.require('/nl/core/sql.js')

logInfo('------------------ STARTS ---------------------------');

var currentStartLine= 0;
var recordsToFetchPerBatch = 5000;
var loop = true;
var processedDeliveries = [];

while(loop){

  var vvTimelineQuery = {};
  vvTimelineQuery.sCompSql = "SELECT W.iDeliveryId FROM " + vars.tableName + " W LIMIT " + recordsToFetchPerBatch + " OFFSET " + currentStartLine + "";

  try {
    vvTimelineQuery.res = sqlSelect("d, @deliveryId:long", vvTimelineQuery.sCompSql.toString());
   
    if(Object.keys(vvTimelineQuery.res.d).length>0){
      currentStartLine = currentStartLine + recordsToFetchPerBatch;
      logInfo('Timelines: ' + Object.keys(vvTimelineQuery.res.d).length);
      
      var delivery;
      for each(var result in vvTimelineQuery.res.d){
        var deliveryId = result.@deliveryId;

        if(deliveryId && deliveryId != "0" && processedDeliveries.indexOf(deliveryId) < 0){
          logInfo("[" + instance.internalName + "] Delivery ID: " + deliveryId);
          processedDeliveries.push(deliveryId);          
          delivery = NLWS.nmsDelivery.load(deliveryId);

          try {
            var updateSQL = "UPDATE " + vars.tableName + " SET sSubjectLine="+NL.SQL.escape(delivery.mailParameters.subject)+ " WHERE iDeliveryId="+NL.SQL.escape(deliveryId)+ "";
            sqlExec(updateSQL);
          } catch(e) {
            logWarning("[" + instance.internalName + "] Error updating worktable with subject line");
            logError("[" + instance.internalName + "] Error number: " + e);
          }          
        }  
      }      
    } else {
        loop = false;
        logInfo("[" + instance.internalName + "] Pagination ends, " + currentStartLine);
    }
  } catch(e) {
    logWarning("[" + instance.internalName + "] Error in getting delivery subject from veeva timeline selected");
    logError("[" + instance.internalName + "] Error number: " + e); // or use logWarning if the treatment must go on despite this error
  }
}
logInfo('------------------ ENDS ---------------------------');

Another example for its use consists of integrations parsing custom date formats (that ACC doesn't support).

Kind regards.

[Sukrity_Wadhwa]

Hi @Sinhue,

Were you able to resolve this query with the given solution? Do let us know.

Thanks!