Hi Community,

How to convert the following SQL query into ESQ so that it can be used in the Script task Process element. 

SELECT TOP 1 SalesOwnerId 

FROM [Lead] 

WHERE SBLBranchLocationId ='060183EE-C2F3-4EDF-853C-59E28026EAD6' 

  AND LeadTypeId='669BC3A8-DFDB-4E53-8AAE-B643C2D6C677' 

  AND SalesOwnerId IS NOT NULL 

GROUP BY SalesOwnerId 

ORDER BY COUNT(SalesOwnerId) ASC

Perform verification whenever a user adds or modifies a daily tour offer as follows: if the total number of active daily tours exceeds the system setting value , saving a record should not be permitted. Instead, a user should receive a message informing that no more than “N” daily tours can be active at a time. “N” is the system setting value.

Here is my code : 

asyncValidate: function(callback, scope) {
    this.callParent([
        function(response) {
            if (!this.validateResponse(response)) {
                return;
            }
            this.validateTravelOfferTour(function(response) {
                if (!this.validateResponse(response)) {
                    return;
                }
                callback.call(scope, response);
            }, this);
        }, this
    ]);
},
 
validateTravelOfferTour: function(callback, scope) {
    // Fetch the Maximum Number Of Daily Periodical Editions System Setting
    var mySetting = Terrasoft.SysSettings.cachedSettings.Usr_Max_Number_Activity_Tours;
    var FrequencyObject=this.get("UsrTravelOfferFrequency");
    if(!FrequencyObject){
        if(callback){
            callback.call(scope,{
                success:true
            });
        }
        return;
    }
    var FrequencyId=FrequencyObject.value;
    //create query
    var esq=this.Ext.create("Terrasoft.EntitySchemaQuery",{
        rootSchemaName:"UsrTravelOffers"
    });
    //esq.addAggregationSchemaColumn("UsrActive",Terrasoft.AggregationType.)
    var ActivityFilter = esq.createColumnFilterWithParameter(0, "UsrActive", "1");
    var frequencyFilter=esq.createColumnFilterWithParameter(0,"UsrTravelOfferFrequency",FrequencyId);
    esq.filters.addItem(frequencyFilter);
    esq.filters.addItem(ActivityFilter);
 
    // Aggregation to get the Count of resulting Query
    esq.addAggregationSchemaColumn("UsrActive", Terrasoft.AggregationType.COUNT, "ActiveOffers");
    //run query
    // Get the entire esq result colelction
    esq.getEntityCollection(function(result) {
        if (result.success && result.collection) {
            // Store the Count of Filtered Records to a variabble
            var items = result.collection.getItems();
            if(items.length>0){
                esqresult=items[0].get("ActiveOffers");
            }
            if(esqresult==mySetting){
                if(callback){
                    callback.call(this,{
                        success:false,
                        message:"no more than 3 daily tours can be active at a time."
                    });
                }
            }else
                if(callback){
                    callback.call(scope,{
                        success:true
                    });
                }
        }
    },this);
},

and this is the error :

{"responseStatus":{"ErrorCode":"InvalidObjectStateException","Message":"Condition with type \"Between\" must contain two expressions in the left part.","Errors":[]},"rowsAffected":-1,"nextPrcElReady":false,"success":false}