OData Cheat Sheet for SQL Users

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

FeatureSQLOData
Number of records in a tableSELECT COUNT(*) FROM Emp/Emps/$count
Querying specific table columnsSELECT ENAME, JOB, SAL FROM Emp/Emps?$select=ENAME,JOB,SAL
Querying the second 5 recordsSELECT * FROM Emp ORDER BY (SELECT NULL)
OFFSET 5 ROWS FETCH FIRST 5 ROWS ONLY
/Emps?$top=5&$skip=5
Ordering DataSELECT * FROM Emp ORDER BY ENAME DESC, SAL/Emps?$orderby=ENAME desc,SAL
Querying joined tablesSELECT * FROM Dept LEFT OUTER JOIN Emp ON
Dept.DEPTNO = Emp.DEPTNO
/Depts?$expand=Emps
Filtering dataSELECT * 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 dataSELECT 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

SQLOData
=eq
!=ne
>gt
>=ge
<lt
<=le
ANDand
ORor
NOTnot
+add
sub
*mul
/div
%mod
()()
IS NULLeq null
IS NOT NULLne 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

SQLOData
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

SQLOData
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

SQLOData
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.

Anna Tereshchenko
Anna Tereshchenko
Technical Writer
10 tip to overcoming Salesforce integration challenges