# OData ### Statements A SELECT statement can consist of the following basic clauses. * SELECT * INTO * FROM * JOIN * WHERE * GROUP BY * HAVING * UNION * ORDER BY * LIMIT SELECT Syntax The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
\|

SELECT {

\[ TOP \ | DISTINCT ]

{

*

| {

\ \[ \[ AS ] \ ]

| { \ | \ } .*

} \[ , ... ]

}

\[ INTO csv:// \[ filename= ] \ \[ ;delimiter=tab ] ]

{

FROM \ \[ \[ AS ] \ ]

} \[ , ... ]

\[ \[

INNER | { { LEFT | RIGHT | FULL } \[ OUTER ] }

] JOIN \ \[ ON \ ] \[ \[ AS ] \ ]

] \[ ... ]

\[ WHERE \ ]

\[ GROUP BY \ \[ , ... ]

\[ HAVING \ ]

\[ UNION \[ ALL ] \ ]

\[

ORDER BY

\ \[ ASC | DESC ] \[ NULLS FIRST | NULLS LAST ]

]

\[

LIMIT \

\[

{ OFFSET | , }

\

]

]

} | SCOPE\_IDENTITY()

\ ::=

| \

| @ \

| ?

| COUNT( \* | { \[ DISTINCT ] \ } )

| { AVG | MAX | MIN | SUM | COUNT } ( \ )

| NULLIF ( \ , \ )

| COALESCE ( \ , ... )

| CASE \

WHEN { \ | \ } THEN { \ | NULL } \[ ... ]

\[ ELSE { \ | NULL } ]

END

| \

| \

\ ::=

{

\ { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } \[ \ ]

} \[ { AND | OR } ... ]

| \| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | #### Examples 1. Return all columns:
| `SELECT * FROM Lead` | | -------------------- | 2. Rename a column:
| `SELECT [FullName] AS MY_FullName FROM Lead` | | -------------------------------------------- | 3. Cast a column's data as a different data type:
| `SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Lead` | | ---------------------------------------------------------------------- | 4. Search data:
| `SELECT * FROM Lead WHERE FirstName <> 'Bartholomew'` | | ----------------------------------------------------- | 5. Return the number of items matching the query criteria:
| `SELECT COUNT(*) AS MyCount FROM Lead` | | -------------------------------------- | 6. Return the number of unique items matching the query criteria:
| `SELECT COUNT(DISTINCT FullName) FROM Lead` | | ------------------------------------------- | 7. Return the unique items matching the query criteria:
| `SELECT DISTINCT FullName FROM Lead` | | ------------------------------------ | 8. Summarize data:
| `SELECT FullName, MAX(AnnualRevenue) FROM Lead GROUP BY FullName` | | ----------------------------------------------------------------- | See Aggregate Functions below for details. 9. Retrieve data from multiple tables.
| `SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId` | | -------------------------------------------------------------------------------------------------------------------- | See JOIN Queries below for details. 10. Sort a result set in ascending order:
| `SELECT Id, FullName FROM Lead ORDER BY FullName ASC` | | ------------------------------------------------------ | 11. Restrict a result set to the specified number of rows:
| `SELECT Id, FullName FROM Lead LIMIT 10` | | ---------------------------------------- | 12. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
| `SELECT * FROM Lead WHERE FirstName = @param` | | --------------------------------------------- | ### JOIN Queries The CData ADO.NET Provider for OData supports standard SQL joins like the following examples. #### Inner Join An inner join selects only rows from both tables that match the join condition:
| `SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId` | | -------------------------------------------------------------------------------------------------------------------- | #### Left Join A left join selects all rows in the FROM table and only matching rows in the JOIN table:
| `SELECT Customers.ContactName, Orders.OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerId=Orders.CustomerId` | | -------------------------------------------------------------------------------------------------------------------------------- | ### Date Literal Functions The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not. #### L\_TODAY() The current day.
| `SELECT * FROM MyTable WHERE MyDateField = L_TODAY()` | | ----------------------------------------------------- | #### L\_YESTERDAY() The previous day.
| `SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()` | | --------------------------------------------------------- | #### L\_TOMORROW() The following day.
| `SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()` | | -------------------------------------------------------- | #### L\_LAST\_WEEK() Every day in the preceding week.
| `SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()` | | --------------------------------------------------------- | #### L\_THIS\_WEEK() Every day in the current week.
| `SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()` | | --------------------------------------------------------- | #### L\_NEXT\_WEEK() Every day in the following week.
| `SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()` | | --------------------------------------------------------- | Also available: * L\_LAST/L\_THIS/L\_NEXT MONTH * L\_LAST/L\_THIS/L\_NEXT QUARTER * L\_LAST/L\_THIS/L\_NEXT YEAR #### L\_LAST\_N\_DAYS(n) The previous n days, excluding the current day.
| `SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)` | | ------------------------------------------------------------ | #### L\_NEXT\_N\_DAYS(n) The following n days, including the current day.
| `SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)` | | ------------------------------------------------------------ | Also available: * L\_LAST/L\_NEXT\_90\_DAYS #### L\_LAST\_N\_WEEKS(n) Every day in every week, starting n weeks before current week, and ending in the previous week.
| `SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)` | | ------------------------------------------------------------- | #### L\_NEXT\_N\_WEEKS(n) Every day in every week, starting the following week, and ending n weeks in the future.
| `SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)` | | ------------------------------------------------------------- | Also available: * L\_LAST/L\_NEXT\_N\_MONTHS(n) * L\_LAST/L\_NEXT\_N\_QUARTERS(n) * L\_LAST/L\_NEXT\_N\_YEARS(n) ### Predicate Functions #### CEILING(value) Returns the value rounded up to the nearest whole number (no decimal component). * expression: The value to round. #### CONCAT(string\_expr1, string\_expr2) Returns the string that is the concatenation of string\_expr1 and string\_expr2. * string\_expr1: The first string to be concatenated. * string\_expr2: The second string to be concatenated. #### CONTAINS(string\_expression, string\_search) Returns true if string\_expression contains string\_expression, otherwise returns false. * string\_expression: The string expression to search within. * string\_search: The value to search for. #### DATE(datetime\_offset) Returns the current date using the specified datetime\_offset. * datetime\_offset: The datetime offset to use when retrieving the current date. #### DAY(datetime\_date) Returns the integer that specifies the day component of the specified date. * datetime\_date: The datetime string that specifies the date. #### ENDSWITH(string\_expression, string\_suffix) Returns true if string\_expression ends with string\_suffix, otherwise returns false. * string\_expression: The string expression to search within. * string\_suffix: The string suffix to search for. #### FLOOR(value) Returns the value rounded down to the nearest whole number (no decimal component). * value: The value to round. #### FRACTIONALSECONDS(datetime\_time) Returns the decimal value that specifies the fractional seconds component of the specified time. * datetime\_time: The datetime string that specifies the time. #### HOUR(datetime\_time) Returns the integer that specifies the hour component of the specified time. * datetime\_time: The datetime string that specifies the time. #### INDEXOF(string\_expression, string\_search) Returns the index location where string\_search is contained within string\_expression. * string\_expression: The string expression to search within. * string\_search: The search value to locate within string\_expression. #### ISOF(string\_expression, string\_type) Returns true if the string\_expression is assignable to type string\_type, otherwise returns false. * string\_expression: The string expression to check the type of. * string\_type: The name of the type. #### LENGTH(string\_expression) Returns the number of characters of the specified string expression. * string\_expression: The string expression. #### MAXDATETIME() Returns the latest possible datetime. #### MINDATETIME() Returns the earliest possible datetime. #### MINUTE(datetime\_time) Returns the integer that specifies the minute component of the specified time. * datetime\_time: The datetime string that specifies the time. #### MONTH(datetime\_date) Returns the integer that specifies the month component of the specified date. * datetime\_date: The datetime string that specifies the date. #### NOW() Returns the current datetime. #### REPLACE(string\_expression, string\_search, string\_replace) Returns the string after replacing any found string\_search values with string\_replace. * string\_expression: The string expression to perform a replace on. * string\_search: The string value to find within string\_expression. * string\_replace: The string value replace and string\_search instances found. #### ROUND(value) Returns the value to the nearest whole number (no decimal component). * value: The value to round. #### SECOND(datetime\_time) Returns the integer that specifies the second component of the specified time. * datetime\_time: The datetime string that specifies the time. #### STARTSWITH(string\_expression, string\_prefix) Returns true if string\_expression starts with string\_prefix, otherwise returns false. * string\_expression: The string expression to search within. * string\_prefix: The string prefix to search for. #### SUBSTRING(string\_expression, integer\_start \[,integer\_length]) Returns the part of the string with the specified length; starts at the specified index. * expression: The character string. * start: The positive integer that specifies the start index of characters to return. * length: The positive integer that specifies how many characters will be returned. #### SUBSTRINGOF(string\_expression, string\_search) Returns true if string\_expression contains string\_expression, otherwise returns false. * string\_expression: The string expression to search within. * string\_search: The value to search for. #### TIME(datetime\_offset) Returns the current time using datetime\_offset. * datetime\_offset: The datetime offset. #### TOLOWER(string\_expression) Returns the string\_expression with the uppercase character data converted to lowercase. * string\_expression: The string expression to lowercase. #### TOTALOFFSETMINUTES(datetime\_date) Returns the integer that specifies the offset minutes component of the specified date. * datetime\_date: The datetime string that specifies the date. #### TOTALSECONDS(duration) Returns the duration value in total seconds. * string\_duration: The duration. #### TOUPPER(string\_expression) Returns the string\_expression with the lowercase character data converted to uppercase. * string\_expression: The string expression to uppercase. #### TRIM(string\_expression) Returns the string\_expression with the leading and trailing whitespace removed. * string\_expression: The string expression to trim. #### YEAR(datetime\_date) Returns the integer that specifies the year component of the specified date. * datetime\_date: The datetime string that specifies the date.