Hi @Milan_Vucetic - attaching some example code here as I cant do it in the comment thread.
If you comment out the line that does the write back (datalayer.writeRecord(record);) , then the query will load all records properly and in the correct batches. If you write back to the temporary schema, then the select will miss a bunch of records.
/**
*
* Adds the bit.ly links and additional notes onto the schema records
*
**/
//Get the schema name (skipping namespace)
var schemaName = vars.targetSchema.substring(vars.targetSchema.indexOf(":") + 1);
var datalayer = {
/**
* Get all Movers records in targetSchema
*
**/
get: {
records: function( recordsToFetchPerBatch,currentStartLine ) {
var query = xtk.queryDef.create(
<queryDef schema={vars.targetSchema} operation="select" lineCount={recordsToFetchPerBatch} startLine={currentStartLine}>
<select>
<node alias="switchDocumentId" expr="@switchDocumentId" />
<node alias="serviceOrderId" expr="@serviceOrderId" />
<node alias="segmentCode" expr="@segmentCode" />
<node alias="completedDate" expr="@completedDate" />
<node alias="scheduledDate" expr="@scheduledDate" />
<node alias="bitly" expr="@bitly" />
<node alias="edmLink" expr="@edmLink" />
<node alias="divisionId" expr="@divisionId" />
<node alias="customerType" expr="@customerType" />
<node alias="IrDescription" expr="@IrDescription" />
<node alias="IrMessage" expr="IrMessage" />
</select>
<where>
<condition expr="@switchDocumentId IS NOT NULL" bool-operator="AND" />
<condition expr="@bitly IS NULL" bool-operator="AND"/>
</where>
</queryDef>
).ExecuteQuery();
return query;
}
},
/**
* Write record back to temp schema
*
*/
writeRecord: function ( data ) {
try {
sqlExec("update " + vars.tableName + " set sbitly='" + data.bitly + "', sedmLink='" + data.edmLink + "', sIrDescription='" + data.IrDescription + "', " + "mIrMessage='" + data.IrMessage + "' where sswitchDocumentId = '" + data.switchDocumentId + "' AND sserviceOrderId = '" + data.serviceOrderId + "';");
} catch (e) {
logError("Something bad happened in writeRecord: " + e);
}
}
}
var businessLogic = {
/**
* Logs all records in dataset
*
*
*/
displayAllRecords : function(records, schemaName) {
logInfo("Dumping all records:");
for (var key in records[schemaName]) {
if (records[schemaName].hasOwnProperty(key)) {
logInfo(key + " -> " + records[schemaName][key]);
}
}
},
/**
* Finds the bit.ly link for the given record
*
**/
findBitlyLinks : function(records, schemaName) {
logInfo("findBitlyLinks - starting...");
for (var key in records[schemaName]) {
var record = records[schemaName][key];
//some business rules in here to determine unique bit.ly and edmLink for each record
record.bitly = "XXXXXX";
record.edmLink = "http://www.somelink.com/blah.html";
}
},
/**
* Create the notes for each set of valid segment codes
*
**/
createIRNotes : function(records, schemaName) {
for (var key in records[schemaName]) {
var record = records[schemaName][key];
switch (segCode) {
case 'MySegCode':
record.IrDescription = 'Something';
record.IrMessage = 'Something Descriptive';
break;
case 'MyOtherSegCode':
record.IrDescription = 'Something Else';
record.IrMessage = 'Something Else Descriptive';
break;
default:
record.IrDescription = 'Missing';
record.IrMessage = 'Missing';
}
//write the record back
datalayer.writeRecord(record);
}
}
}
var currentStartLine = 0;
var recordsToFetchPerBatch = 200;
var loop = true;
var records = {};
while(loop){
//get the records from the targetSchema
logInfo("Getting batch from " + currentStartLine + " to " + (currentStartLine+recordsToFetchPerBatch));
records = datalayer.get.records(recordsToFetchPerBatch,currentStartLine);
logInfo("Got " + Object.keys(records).length + " records");
if( Object.keys(records).length > 0 ) {
//do the Bit.ly links
businessLogic.findBitlyLinks(records, schemaName);
//create IR Notes (this is where the write happens)
businessLogic.createIRNotes(records, schemaName);
//increment loop
currentStartLine += recordsToFetchPerBatch;
records = {};
} else {
logInfo("Exiting..");
loop = false;
}
}
