MyRapidi
MyRapidi
Menu

Search our Wiki

Filtering with ODATA

SQL operators are used to perform operations on data within a database. They help in filtering, aggregating, and manipulating data, making them essential for querying and reporting in a D365 Business Central database. Here’s a list of commonly used SQL operators along with examples relevant to D365 Business Central scenarios:

Note: When setting up any filter, the source field must be added in the field FIELD NAME. Then, the actual filter must be added in the field FILTER where the format should be : Field Name + SQL operator + value/expression (see examples below).

  • Equal To (denoted as = or EQ) is used to filter on a specific record on the source system. You can either user the EQ operator or simply add the value that you are filtering on in the field called FILTER without the EQ operator. 

 No eq '40000'

Country_Region_Code eq 'ES' AND Country_Region_Code eq 'US'

Country_Region_Code eq 'ES' OR Country_Region_Code eq 'US'

  • Greater Than & Less Than ( denoted as > or LT) / Greater than (denoted as > or GT) are used to compare values in a database table and retrieve rows where a specific column value is greater than or less than to a given criteria.

Entry_No gt 610 and Entry_No lt 615

Entry_No lt 610

Entry_No gt 610

  • IN  is used when you want to specify multiple values and limit the filter to those specific values. 

Entry_No in (610, 612, 614)


  • Greater than or Equal to ( denoted as >= or GE) is used to compare values in a database table and retrieve rows where a specific column value is greater than or equal to a given criteria.

Entry_No ge 610

  • Less than or Equal to ( denoted as <= or LT) is used to compare values in a database table and retrieve rows where a specific column value is less than or equal to a given criteria.

Entry_No le 610

  • Not Equal to ( denoted as <> , NE or !=) is used to compare values in a database table and retrieve rows where a specific column's value does not match a given criteria.

VAT_Bus_Posting_Group ne 'EXPORT'

  • Endswith - indicates whether or not the source string ends with the search string and will retrieve only the records that meet the criteria. 

endswith(VAT_Bus_Posting_Group,'RT')


  • Startswith - indicates whether or not the source string starts with the search string and will retrieve only the records that meet the criteria. 

startswith(Name, 'S')


  • Contains - it will return records where the name contains the string “urn”.

contains(Name, 'urn')

  • Substring - it will return records with the string RED in their location code starting as position 5.

substring(Location_Code, 5) eq 'RED'

 

  • Tolower - it will take the string and convert it into all lower case letters.

 tolower(Location_Code) eq 'code red'


  • Toupper and it will take the string and convert it into all lower case letters.

 toupper(FText) eq '2ND ROW'