Why is the SSJS constructed query not updating?

Question

I'm trying to construct a query using SSJS and update the quarry activity.

1. Using Core SSJS: When I'm using the variable containing the new query string, I get a smile error with now details:

Error

2. Using WSProxy: There is no error, but no change in query. Here is the response:

{"Status":"OK","RequestID":"1234","Results":[{"Object":{"Name":null,"DataExtensionTarget":{"Name":"TEST JourneyMonitorHourLog","Description":null,"Keyword":null,"Client":null,"PartnerKey":null,"PartnerProperties":null,"CreatedDate":"0001-01-01T00:00:00.000","ModifiedDate":null,"ID":0,"ObjectID":null,"CustomerKey":"C9E17EF0-99E7-4D3E-83B8-0B65DCEBE29D","Owner":null,"CorrelationID":null,"ObjectState":null,"IsPlatformObject":false},"ObjectID":"a68c6d18-77ae-45cf-a5de-311d1fe774d6","CategoryID":4771,"QueryText":null,"TargetType":"DE","TargetUpdateType":"Update","FileSpec":null,"FileType":null,"Status":null,"InteractionObjectID":null,"Description":"My updated query","Keyword":null,"Client":null,"PartnerKey":null,"PartnerProperties":null,"CreatedDate":"0001-01-01T00:00:00.000","ModifiedDate":null,"ID":0,"CustomerKey":null,"Owner":null,"CorrelationID":null,"ObjectState":null,"IsPlatformObject":false},"UpdateResults":null,"ParentPropertyName":null,"StatusCode":"OK","StatusMessage":"QueryDefinition updated","OrdinalID":0,"ErrorCode":0,"RequestID":null,"ConversationID":null,"OverallStatusCode":null,"RequestType":"Synchronous","ResultType":null,"ResultDetailXML":null}]}

3. When I Write(sqlQuery), copy, and hard code the query, it works!

Constructing the quarry:

try {
    var sqlQuery = ""
    var de = DataExtension.Init("12121212");
    var deData = de.Rows.Retrieve();

    if (deData.length > 0) {
        for (var i = 0; i < deData.length; i++) {
            if (i > 0) {
                sqlQuery += " UNION ";
            }

            sqlQuery += "SELECT '" + deData[i].JourneyID + "' as JourneyID, COUNT(*) AS DEEntered ";
            sqlQuery += "FROM [" + deData[i].JourneyDEName + "] ";
            sqlQuery += "WHERE CAST(System_Date AT TIME ZONE 'UTC' AT TIME ZONE 'Israel Standard Time' AS DATE) = ";
            sqlQuery += "CAST(GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Israel Standard Time' AS DATE)";
        }

        Write("Generated SQL Query:<br><pre>" + sqlQuery + "</pre>");
    }
} catch (error) {
    Write("An error occurred: " + error);
}

Core SSJS

var customerKey = "monitor-total-sums"
var qd = QueryDefinition.Init(customerKey);

try {
    var result = qd.Update({
        QueryText: sqlQuery
    });
    Write("Update query res: " + Stringify(result));

} catch (error) {
    Write("Update query Error: " + Stringify(error));

}

WSProxy

var config = {
        ObjectID: objectID,
        Description: "My updated query",
        CategoryID: categoryID,
        TargetType: "DE",
        TargetUpdateType: "Update",
        QueryText: sqlQuery,
        DataExtensionTarget: {
            Name: "TEST JourneyMonitorHourLog",
            CustomerKey: targetKey
        }
    };

    var result = api.updateItem("QueryDefinition", config);
    Write(Stringify(result));

I assume this is an issue with escaping or quoting problems.

Any ideas on how to solve this?

Answer 1

Found the issue, the var sqlQuery = "" was in a try/catch scope. So the actual function received an empty variable.

Here is the updated part:

  var sqlQuery = ""
try {
    var de = DataExtension.Init("1234");
    var deData = de.Rows.Retrieve();

    if (deData.length > 0) {
        for (var i = 0; i < deData.length; i++) {
            if (i > 0) {
                sqlQuery += " UNION ";
            }

            sqlQuery += "SELECT '" + deData[i].JourneyID + "' as JourneyID, COUNT(*) AS DEEntered ";
            sqlQuery += "FROM [" + deData[i].JourneyDEName + "] ";
            sqlQuery += "WHERE CAST(System_Date AT TIME ZONE 'UTC' AT TIME ZONE 'Israel Standard Time' AS DATE) = ";
            sqlQuery += "CAST(GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Israel Standard Time' AS DATE)";
        }

        Write("Generated SQL Query:<br><pre>" + sqlQuery + "</pre>");
    }
} catch (error) {
    Write("An error occurred: " + error);
}