cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Using data filter widget for creating dynamic SQL queries

sgobi
5-Regular Member

Using data filter widget for creating dynamic SQL queries

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

3 REPLIES 3
PaiChung
21-Topaz II
(To:sgobi)

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.

sgobi
5-Regular Member
(To:PaiChung)

Hello Pai,

What I am looking for is BETWEEN like the below NE case statement.

case "GE":

              queryString = queryString + " " + filterObj.fieldName + " >= " + filterObj.value + " ";

PaiChung
21-Topaz II
(To:sgobi)

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.

Announcements