OData is a widely accepted open standard for data access over the Internet. OData protocol provides powerful features for querying data via URLs, similar to SQL. This article helps to quickly understand how to query data via OData and shows how OData features correspond to the most commonly used SQL features.
MAIN ODATA FEATURES
| Feature | SQL | OData |
| Number of records in a table | SELECT COUNT(*) FROM Emp | /Emps/$count |
| Querying specific table columns | SELECT ENAME, JOB, SAL FROM Emp | /Emps?$select=ENAME,JOB,SAL |
| Querying the second 5 records | SELECT * FROM Emp ORDER BY (SELECT NULL) OFFSET 5 ROWS FETCH FIRST 5 ROWS ONLY | /Emps?$top=5&$skip=5 |
| Ordering Data | SELECT * FROM Emp ORDER BY ENAME DESC, SAL | /Emps?$orderby=ENAME desc,SAL |
| Querying joined tables | SELECT * FROM Dept LEFT OUTER JOIN Emp ON Dept.DEPTNO = Emp.DEPTNO | /Depts?$expand=Emps |
| Filtering data | SELECT * FROM EMP WHERE (SAL/2 > 500 AND HIREDATE <= ’01/01/1985′) OR (COMM IS NOT NULL AND ENAME LIKE ‘J%’) | /Emps?$filter=(SAL div 2 gt 500 and HIREDATE le 1985-01-01) or (COMM ne null and startswith(ENAME,’J’)) |
| Aggregating data | SELECT SUM(SAL) AS Sum, MAX(SAL) AS Max, Min(SAL) AS Min, AVG(Sal) AS Avg FROM Emp | /Emps?$apply=aggregate(SAL with sum as Sum,SAL with max as Max,SAL with min as Min,SAL with average as Avg) |
Filter Expressions
OData protocol supports a number of different mathematical, logical, etc., operators and functions in the $filter expression. Here you can find a brief list of these operators and functions that you can use in your OData requests together with their SQL analogs. Note that they are case-sensitive in OData requests.
OPERATORS
| SQL | OData |
| = | eq |
| != | ne |
| > | gt |
| >= | ge |
| < | lt |
| <= | le |
| AND | and |
| OR | or |
| NOT | not |
| + | add |
| – | sub |
| * | mul |
| / | div |
| % | mod |
| () | () |
| IS NULL | eq null |
| IS NOT NULL | ne null |
| X LIKE ‘%Y%’ | OData v1 – v3: substringof(‘Y’,X) OData v4: contains(X,’Y’) |
| X LIKE ‘Y%’ | startswith(X,’Y’) |
| X LIKE ‘%Y’ | endswith(X,’Y’) |
STRING FUNCTIONS
| SQL | OData |
| LEN(X) | length(X) |
| CHARINDEX(X,’Y’) | indexof(X,’Y’) |
| REPLACE(X,’Y’,’Z’) | replace(X,’Y’,’Z’) |
| SUBSTRING(X,2,3) | substring(X,2,3) |
| LOWER(X) | tolower(X) |
| UPPER(X) | toupper(X) |
| TRIM(X) | trim(X) |
| CONCAT(X,Y) | concat(X,Y) |
DATE FUNCTIONS
| SQL | OData |
| DATEPART(year,X) | year(X) |
| DATEPART(month,X) | month(X) |
| DATEPART(day,X) | day(X) |
| DATEPART(hour,X) | hour(X) |
| DATEPART(minute,X) | minute(X) |
| DATEPART(second,X) | second(X) |
MATHEMATICAL FUNCTIONS
| SQL | OData |
| ROUND(X) | round(X) |
| FLOOR(X) | floor(X) |
| CEILING(X) | ceiling(X) |
Skyvia Connect
Skyvia Connect is an OData server-as-a-service solution that allows creating an OData interface for your data, stored in various data sources, via drag-n-drop in just a couple of minutes. It creates endpoints supporting all the OData features listed here and more. Publish your data via Skyvia Connect and try the listed OData features. Skyvia Connect provides endpoint access control and logging features, and you can use Skyvia Connect endpoints in a wide range of OData consumer applications.
