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

Avatar

Avatar
Boost 1
Level 1
Sinhue
Level 1

Like

1 like

Total Posts

6 posts

Correct reply

0 solutions
Top badges earned
Boost 1
Validate 1
View profile

Avatar
Boost 1
Level 1
Sinhue
Level 1

Like

1 like

Total Posts

6 posts

Correct reply

0 solutions
Top badges earned
Boost 1
Validate 1
View profile
Sinhue
Level 1

13-10-2020

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.

 

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Affirm 100
MVP
_Manoj_Kumar
MVP

Likes

127 likes

Total Posts

329 posts

Correct reply

104 solutions
Top badges earned
Affirm 100
Validate 1
Contributor
Ignite 1
Shape 1
View profile

Avatar
Affirm 100
MVP
_Manoj_Kumar
MVP

Likes

127 likes

Total Posts

329 posts

Correct reply

104 solutions
Top badges earned
Affirm 100
Validate 1
Contributor
Ignite 1
Shape 1
View profile
_Manoj_Kumar
MVP

13-10-2020

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

 

 

Answers (1)

Answers (1)

Avatar

Avatar
Give Back 50
Employee
Sukrity_Wadhwa
Employee

Likes

175 likes

Total Posts

934 posts

Correct reply

35 solutions
Top badges earned
Give Back 50
Validate 1000
Validate 500
Validate 250
Validate 100
View profile

Avatar
Give Back 50
Employee
Sukrity_Wadhwa
Employee

Likes

175 likes

Total Posts

934 posts

Correct reply

35 solutions
Top badges earned
Give Back 50
Validate 1000
Validate 500
Validate 250
Validate 100
View profile
Sukrity_Wadhwa
Employee

26-10-2020

Hi @Sinhue,

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

Thanks!