# Excel Services
### SELECT 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 Account` |
| ----------------------- |
2. Rename a column:
| `SELECT [Name] AS MY_Name FROM Account` |
| --------------------------------------- |
3. Cast a column's data as a different data type:
| `SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Account` |
| ------------------------------------------------------------------------- |
4. Search data:
| `SELECT * FROM Account WHERE Industry = 'Floppy Disks';` |
| -------------------------------------------------------- |
5. Return the number of items matching the query criteria:
| `SELECT COUNT(*) AS MyCount FROM Account` |
| ----------------------------------------- |
6. Return the number of unique items matching the query criteria:
| `SELECT COUNT(DISTINCT Name) FROM Account` |
| ------------------------------------------ |
7. Return the unique items matching the query criteria:
| `SELECT DISTINCT Name FROM Account` |
| ----------------------------------- |
8. Summarize data:
| `SELECT Name, MAX(AnnualRevenue) FROM Account GROUP BY Name` |
| ------------------------------------------------------------ |
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, Name FROM Account ORDER BY Name ASC` |
| ------------------------------------------------- |
11. Restrict a result set to the specified number of rows:
| `SELECT Id, Name FROM Account 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 Account WHERE Industry = @param` |
| ----------------------------------------------- |
### Aggregate Functions
#### COUNT
Returns the number of rows matching the query criteria.
| `SELECT COUNT(*) FROM Account WHERE Industry = 'Floppy Disks'` |
| -------------------------------------------------------------- |
#### COUNT(DISTINCT)
Returns the number of distinct, non-null field values matching the query criteria.
| `SELECT COUNT(DISTINCT Id) AS DistinctValues FROM Account WHERE Industry = 'Floppy Disks'` |
| ------------------------------------------------------------------------------------------ |
#### AVG
Returns the average of the column values.
| `SELECT Name, AVG(AnnualRevenue) FROM Account WHERE Industry = 'Floppy Disks'` `GROUP BY Name` |
| ----------------------------------------------------------------------------------------------- |
#### MIN
Returns the minimum column value.
| `SELECT MIN(AnnualRevenue), Name FROM Account WHERE Industry = 'Floppy Disks'` `GROUP BY Name` |
| ---------------------------------------------------------------------------------------------- |
#### MAX
Returns the maximum column value.
| `SELECT Name, MAX(AnnualRevenue) FROM Account WHERE Industry = 'Floppy Disks'` `GROUP BY Name` |
| ---------------------------------------------------------------------------------------------- |
#### SUM
Returns the total sum of the column values.
| `SELECT SUM(AnnualRevenue) FROM Account WHERE Industry = 'Floppy Disks'` |
| ------------------------------------------------------------------------ |
### JOIN Queries
The Provider for Excel Services 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.
#### 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.
#### 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.
#### LENGTH(string\_expression)
Returns the number of characters of the specified string expression.
* string\_expression: The string expression.
#### 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.
#### 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.
#### 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.
#### TOLOWER(string\_expression)
Returns the string\_expression with the uppercase character data converted to lowercase.
* string\_expression: The string expression to lowercase.
#### 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.