locked
oDataQuery $filter RRS feed

  • Question

  • Hi,

    I have a table, that contains string column "n_Number" with length 25. In that column there are some big numbers.

    I need to query numbers like _123%

    According two pages:

    http://msdn.microsoft.com/en-us/library/gg309461.aspx#BKMK_select

    http://msdn.microsoft.com/en-us/library/hh169248%28v=nav.71%29.aspx

    I made a query from my table:

    ?$select=n_Number&$filter=substring(n_Number, 1, 3) eq '123'

    But server response: Invalid 'where' condition. An entity member is invoking an invalid property or method.

    What am i doing wrong?


    Friday, January 16, 2015 12:03 PM

Answers

  • Hi,

    This will do the trick:

    filter=startswith(n_Number,'_123')


    Regards, Saad

    • Marked as answer by xjomanx Tuesday, January 20, 2015 7:11 AM
    Tuesday, January 20, 2015 6:32 AM
  • Hi,

    This will do the trick:

    filter=startswith(n_Number,'_123')


    Regards, Saad

    As i said upper:

    Example:

    I do not need to query number 123242690876. Just 412394826001.

    Thanks to all for trying help, i already found decision of my problem. I wrote a function on IIS:

    public string CountEntities(string EntityName, string WhereStatment)
    {
        if (EntityName.Length == 0)
        {
            return "Error;EntityNanme must be filled";
        }
        else
        {
            try
            {
                string ConnectionString = "Data Source=server;Initial Catalog=catalog;Integrated Security=SSPI;UID=login;Password=password"
                SqlConnection conn = new SqlConnection(ConnectionString);
                string SqlText = "Select count(1) from " + EntityName;
                if (WhereStatment.Length > 0)
                {
                    SqlText += " where " + WhereStatment;
                }
                SqlCommand cmdCount = new SqlCommand(SqlText, conn);
                cmdCount.Connection.Open();
                SqlDataReader reader = cmdCount.ExecuteReader();
                reader.Read();
                return "Done;" + reader[0].ToString();
            }
            catch (Exception ex)
            {
                return "Error;" + ex.Message;
            }
        }
    }

    Then js simple call this function throw a link:

    http(s)://server/catalog/project.svc/CountEntities?EntityName=My_Entity_Name&WhereStatment=substring(n_Number,2,3) = '123'

    Fast result and i can place any SQL condition to my request.


    • Marked as answer by xjomanx Tuesday, January 20, 2015 6:54 AM
    • Edited by xjomanx Tuesday, January 20, 2015 6:55 AM
    Tuesday, January 20, 2015 6:54 AM

All replies

  • Hi,

    Try with..

    $filter=substringof('123',n_Number)

    https://crm2011odatatool.codeplex.com/

    Thanks


    Friday, January 16, 2015 12:48 PM
  • This code is working, but it returns entities, that contains %123% but i need, to "123" contains strongly in 2,3,4 positions respectively.

    Example:

    I do not need to query number 645212390876. Just 412394826001.

    Friday, January 16, 2015 1:06 PM
  • This code is working, but it returns entities, that contains %123% but i need, to "123" contains strongly in 2,3,4 positions respectively.

    Example:

    I do not need to query number 645212390876. Just 412394826001.

    as far as I know this is not possible, but I will be happy to be proven wrong

    My blog: www.crmanswers.net - Rockstar 365 Profile

    Friday, January 16, 2015 1:15 PM
  • I will be happy too, because There are very much entities to load in JS massive and process entities there. It will be too long. And i just need to count some entities. This is a second operation on the serves side. :(
    Friday, January 16, 2015 1:34 PM
  • Hi,

    Sorry, give one more try.

    $filter=(substringof('123',substring(tolower(n_Number),1,3)))

    If this not work then agree with Guido.

    Friday, January 16, 2015 2:05 PM
  • Hi,

    How many objects do you get with the %123%-filter? Is it a shitload och just many?
    One solution would be to go with the %123%-filter and the work with the resulting set so sort out the true set, however, if there are many records this would probably give a serious lag (and it's a sort of an ugly solution)

    Regards


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Monday, January 19, 2015 3:23 PM
  • It's a bit fiddly but could you just search for '0123..' OR '1123' OR '2123'?
    Monday, January 19, 2015 4:38 PM
  • No, I already made my function on IIS, which count a number of entities with needed conditions. And js simple call this function.
    • Edited by xjomanx Monday, January 19, 2015 6:49 PM
    Monday, January 19, 2015 6:44 PM
  • Hi,

    This will do the trick:

    filter=startswith(n_Number,'_123')


    Regards, Saad

    • Marked as answer by xjomanx Tuesday, January 20, 2015 7:11 AM
    Tuesday, January 20, 2015 6:32 AM
  • Hi,

    This will do the trick:

    filter=startswith(n_Number,'_123')


    Regards, Saad

    As i said upper:

    Example:

    I do not need to query number 123242690876. Just 412394826001.

    Thanks to all for trying help, i already found decision of my problem. I wrote a function on IIS:

    public string CountEntities(string EntityName, string WhereStatment)
    {
        if (EntityName.Length == 0)
        {
            return "Error;EntityNanme must be filled";
        }
        else
        {
            try
            {
                string ConnectionString = "Data Source=server;Initial Catalog=catalog;Integrated Security=SSPI;UID=login;Password=password"
                SqlConnection conn = new SqlConnection(ConnectionString);
                string SqlText = "Select count(1) from " + EntityName;
                if (WhereStatment.Length > 0)
                {
                    SqlText += " where " + WhereStatment;
                }
                SqlCommand cmdCount = new SqlCommand(SqlText, conn);
                cmdCount.Connection.Open();
                SqlDataReader reader = cmdCount.ExecuteReader();
                reader.Read();
                return "Done;" + reader[0].ToString();
            }
            catch (Exception ex)
            {
                return "Error;" + ex.Message;
            }
        }
    }

    Then js simple call this function throw a link:

    http(s)://server/catalog/project.svc/CountEntities?EntityName=My_Entity_Name&WhereStatment=substring(n_Number,2,3) = '123'

    Fast result and i can place any SQL condition to my request.


    • Marked as answer by xjomanx Tuesday, January 20, 2015 6:54 AM
    • Edited by xjomanx Tuesday, January 20, 2015 6:55 AM
    Tuesday, January 20, 2015 6:54 AM
  • Hi,

    I tried below filter and it returned number which contains '123' starting at 2nd position.

    filter=startswith(n_Number,'_123')


    Regards, Saad


    Regards, Saad

    Tuesday, January 20, 2015 7:04 AM
  • Sorry, I didn't see a _ before number.

    This code works fine, thank you!

    Tuesday, January 20, 2015 7:13 AM