2 Replies Latest reply on Jul 6, 2017 12:06 PM by wposner-2 RSS
    rkandasamy-2 Newbie

    QueryDataTableEntries with detailed query parameters

    Hi Team,

     

    we have a scenario as below where we have a number of fields as follows,

     

    OrderNumber,Customer,BoardsArray,Status.

     

    We have the following values in that,

     

    ('Order1','Client1','{"SN1", "SN2"}','ACTIVE');

    ('Order2','Client2','{"SN2", "SN3","SN4"}','ACTIVE');

    ('Order3','Client3','{"SN2", "SN3","SN4","SN5","SN6","SN7","SN8","SN9","SN10"}','ACTIVE');

    ('Order4','Client4','{"SN1", "SN7"}','ACTIVE');

     

    in postgreSQL, it is possible to query even based on the array items as follows,

    SELECT *from TableName   where  'SN1' = ANY (BoardsArray::text[]);  (which returns records of Order1 and Order4)

     

    Present option:

    1. QueryDataTableEntries of that table without any condition

    2. Loop through that result and find out each BoardsArray in that records has this value

     

    But present way is costly as it brings all the records into the memory and performs the operation on it. Please let me know if there any better way to do the same.

     

    Is there any way the same can be done in Thingworx with what thingworx filters are given?