3 Replies Latest reply on May 24, 2016 2:49 PM by pchung RSS
    sabarish Apprentice

    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