OData Cheat Sheet for SQL Users

November 13, 2019

OData is a widely accepted open standard for data access over the Internet. OData protocol provides powerful features for querying data via URLs, much 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.