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

Community Tip - Did you get called away in the middle of writing a post? Don't worry you can find your unfinished post later in the Drafts section of your profile page. X

Using data filter widget for creating dynamic SQL queries

sgobi
6-Contributor

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
22-Sapphire I
(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
6-Contributor
(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
22-Sapphire I
(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.

Top Tags