Community Tip - You can subscribe to a forum, label or individual post and receive email notifications when someone posts a new topic or reply. Learn more! X
Hi
We have been using Data Filter widget to create SQL queries dynamically.
It works Except for Dates. If my SQL Query has a Timestamp field, then Data Filter will not work.
We are using below Script to create Filters.
var queryString = " ";
for each (var filterObj in filters) {
if (queryString.length > 1) queryString = queryString + " AND ";
switch(filterObj.type) {
case "EQ":
queryString = queryString + " " + filterObj.fieldName + " = '" + [[filterObj.value]] + "' ";
break;
case "LIKE":
var value = filterObj.value.toString();
//logger.warn(value);
value = value.replace("*", "%");
value = value.replace("*", "%");
queryString = queryString + " " + filterObj.fieldName + " LIKE '" + value + "' ";
break;
case "NE":
queryString = queryString + " " + filterObj.fieldName + " <> " + filterObj.value + " ";
break;
case "GT":
queryString = queryString + " " + filterObj.fieldName + " > " + filterObj.value + " ";
break;
case "LT":
queryString = queryString + " " + filterObj.fieldName + " < " + filterObj.value + " ";
break;
case "GE":
queryString = queryString + " " + filterObj.fieldName + " >= " + filterObj.value + " ";
break;
case "LE":
queryString = queryString + " " + filterObj.fieldName + " <= " + filterObj.value + " ";
break;
default:
queryString = queryString + " " + filterObj.fieldName + " = '" + filterObj.value + "' ";
Are there anyone using my approach to dynamically create SQL queries?
Appreciate any Help and Comments!
TIA
Saby
I think when you log out the information, you probably will notice that SQL Server doesn't like the way the Date Time stamp is formatted in Thingworx vs. used in SQL server, so you will need to transform those.
Hello Pai,
What I am looking for is BETWEEN like the below NE case statement.
case "GE":
queryString = queryString + " " + filterObj.fieldName + " >= " + filterObj.value + " ";
Not quite sure about your question. you can fairly easily log out the json that the data filter generates
for example:
{"filters":{"filters":[{"fieldName":"lastConnection","from":1464086857553,"to":1464115657553,"type":"BETWEEN"},{"fieldName":"Pushed_Cos_Number3","type":"GT","value":45}],"type":"AND"}}
this is a filter on both lastConnection and Pushed_Cos_Number3, one is a between and the other a Greater then. As you can see time is represented as milliseconds from epoch.