Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
The Amazon Athena APIs support the following operators in the WHERE clause: =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, ANY, ALL, EXISTS, NOT EXISTS, CONTAINS, NOT CONTAINS, BETWEEN, AND, OR.
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
In Amazon Athena, INNER and OUTER joins are supported.
The following examples use tables in the Northwind database to show how to join Amazon Athena tables:
The query below returns the ContactName and the OrderDate of every customer who has an order:
Returns the largest of the provided values.
value1: The first value to compare.
value2: The second value to compare.
Returns the smallest of the provided values.
value1: The first value to compare.
value2: The second value to compare.
Returns the value of the first non-null expression. The remaining expressions are not evaluated. All input expressions must be implicitly coercible to a common supertype.
expr1: Any expression
expr2: Any expression
Returns NULL if expression = expression_to_match is true, otherwise returns expression. expression and expression_to_match must be implicitly coercible to a common supertype; equality comparison is done on coerced values.
expression: Any expression
expression_to_match: Any expression to be matched
Cast is used in a query to indicate that the result type of an expression should be converted to some other type.
expression: The expression to cast.
type: The type to cast the expression to.
Like CAST, but returns NULL if the cast fails.
expression: The expression to cast.
type: The type to cast the expression to.
Returns the name of the type of the provided expression
expr: An expression.
Returns the absolute value of the argument.
expression: Any column or literal expression.
Returns the cube root of expression.
expression: Any column or literal expression.
Rounds the argument up to the nearest whole number and returns the rounded value.
expression: Any column or literal expression.
Synonym for CEIL function.
expression: Any column or literal expression.
Returns expression, converted from radians to degrees.
expression: Any column or literal expression.
Returns the result of raising the constant "e" - the base of the natural logarithm - to the power of expression.
expression: Any column or literal expression.
Rounds the argument down to the nearest whole number and returns the rounded value.
expression: Any column or literal expression.
Returns the value of string interpreted as a base-radix number.
string: A string column or literal expression.
radix: An optional radix.
Returns the natural logarithm of the argument.
expression: Any column or literal expression.
Returns the natural logarithm of the argument.
expression: Any column or literal expression.
base: The base to use when performing the logarithm.
Returns the Base-2 logarithm of the argument.
expression: Any column or literal expression.
Returns the Base-10 logarithm of the argument.
expression: Any column or literal expression.
Returns the modulus (remainder) of n divided by m.
n: Any column or literal expression.
m: Any column or literal expression.
Returns PI.
Returns the result of raising expression1 to the power of expression2.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
Synonym of POW function.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
Returns expression, converted from degrees to radians.
expression: Any column or literal expression.
Returns a pseudo-random number in the range 0.0 >= value < expression. If expression is not specified, the value is 1.0.
expression: Any column or literal expression.
Rounds the argument either up or down to the nearest whole number (or if specified, to the specified number of digits) and returns the rounded value.
expression: Any column or literal expression.
integer_digits: The number of digits to round to.
Returns the signum function of expression, that is: 0 if the argument is 0, 1 if the argument is greater than 0, -1 if the argument is less than 0.
expression: Any column or literal expression.
Returns the square root of the expression. Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
Returns the base-radix representation of expression.
expression: Any column or literal expression.
radix: An optional radix.
Returns expression rounded to integer by dropping digits after decimal point.
expression: Any column or literal expression.
Returns the arc cosine of the argument.
expression: Any column or literal expression.
Returns arcsine in radians.
expression: Any column or literal expression.
Returns arc tangent of the argument.
expression: Any column or literal expression.
Returns the arc tangent of the two arguments.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
Returns the cosine of the argument.
expression: Any column or literal expression.
Returns the hyperbolic cosine of the argument.
expression: Any column or literal expression.
Returns the sine of the argument.
expression: Any column or literal expression.
Returns the tangent of the argument.
expression: Any column or literal expression.
Returns the hyperbolic tangent of the argument.
expression: Any column or literal expression.
Returns the bitwise AND of x and y in 2's complement representation.
x: A numeric expression to perform the bitwise operation.
y: A numeric expression to perform the bitwise operation.
Count the number of bits set in expression (treated as bits-bit signed integer) in 2's complement representation.
expression: The expression to perform the bit count operation on.
bits: The number of bits.
Returns the bitwise NOT of x in 2's complement representation.
x: A numeric expression to perform the bitwise operation.
Returns the bitwise OR of x and y in 2's complement representation.
x: A numeric expression to perform the bitwise operation.
y: A numeric expression to perform the bitwise operation.
Returns the bitwise XOR of x and y in 2's complement representation.
x: A numeric expression to perform the bitwise operation.
y: A numeric expression to perform the bitwise operation.
Returns the Unicode code point n as a single character string.
n: Unicode code point value.
Returns the Unicode code point of the only character of expression.
expression: Any column or literal expression.
Returns the concatenation of two or more strings, or NULL if any of the values are NULL.
str1: The first string to concatenate.
str2: The second string to concatenate.
str3: The third string to concatenate.
Returns a numerical value for the length of the string. Example: if str is '123456', LENGTH returns 6.
str: The string to calculate the length on.
Returns the original string with all characters in lower case.
str: The string to lower.
Pads str1 on the left with str2, repeating str2 until the result string is exactly numeric_expr characters. Example: LPAD('1', 7, '?') returns ??????1.
str1: The string to pad.
numeric_expression: The number of str2 instances to pad.
str2: The pad characters.
Removes characters from the left side of str1.
str1: The string to trim.
Replaces all instances of from_value within original_value with to_value.
original_value: The string to search in.
from_value: The string to search for.
to_value: The string to replace instances of from_value.
Returns the reverse of the input STRING or BYTES.
str: The string to reverse.
Pads str1 on the right with str2, repeating str2 until the result string is exactly numeric_expr characters. Example: RPAD('1', 7, '?') returns 1??????.
str1: The string to pad.
numeric_expression: The number of str2 instances to pad.
str2: The pad characters.
Removes trailing characters from the right side of str1.
str: The string to trim.
Splits a string into repeated substrings. If delimiter is specified, the SPLIT function breaks str into substrings, using delimiter as the delimiter.
str: The string to split.
delimiter: The delimiter to split the string on. Default delimiter is a comma (,).
limit: The maximum number of values to return.
Splits str on delimiter and returns the field index. Field indexes start with 1. If the index is larger than than the number of fields, then null is returned.
str: The string to split.
delimiter: The delimiter to split the string on. Default delimiter is a comma (,).
index: The index to return.
Returns the 1-based index of the first occurrence of value2 inside value1. Returns 0 if value2 is not found.
str1: The string to search in.
str2: The string to search for.
Returns a substring of str, starting at index. If the optional max_len parameter is used, the returned string is a maximum of max_len characters long. Counting starts at 1, so the first character in the string is in position 1 (not zero). If index is 5, the substring begins with the 5th character from the left in str. If index is -4, the substring begins with the 4th character from the right in str. Example: SUBSTR('awesome', -4, 4) returns the substring some.
str: The original string.
index: The starting index.
max_len: The maximum length of the return string.
Removes leading and trailing whitespace from str.
str: The string to trim.
Returns the original string with all characters in upper case.
str: The string to upper.
Encodes str into a UTF-8 varbinary representation.
str: The string to convert to UTF-8
Decodes a UTF-8 encoded string from binary. Invalid UTF-8 sequences are replaced with replace. The replacement string replace must either be a single character or empty (in which case invalid characters are removed).
expression:
replace: The string to replace invalid UTF-8 sequences.
Converts a sequence of BYTES into a base64-encoded STRING. To convert a base64-encoded STRING into BYTES, use FROM_BASE64.
string_expr: The string to convert to base64 encoding.
Converts the base64-encoded input string_expr into BYTES format. To convert BYTES to a base64-encoded STRING, use TO_BASE64.
string_expr: The string to convert from base64 encoding.
Encodes binary into a base64 string_expr representation using the URL safe alphabet.
string_expr: The string to convert from base64 encoding.
Decodes binary data from the base64 encoded string_expr using the URL safe alphabet.
string_expr: The string to convert from base64 encoding.
Converts a sequence of BYTES into a hexadecimal STRING. Converts each byte in the STRING as two hexadecimal characters in the range (0..9, a..f). To convert a hexadecimal-encoded STRING to BYTES, use FROM_HEX.
string_expr: The string to convert to hexadecimal encoding.
Converts a hexadecimal-encoded STRING into BYTES format. Returns an error if the input STRING contains characters outside the range (0..9, A..F, a..f). The lettercase of the characters does not matter. To convert BYTES to a hexadecimal-encoded STRING, use TO_HEX.
string_expr: The string to convert from hexadecimal encoding.
Computes the hash of the input using the MD5 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 16 bytes.
expression: The expression to use to compute the hash.
Computes the hash of the input using the SHA-1 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 20 bytes.
expression: The expression to use to compute the hash.
Computes the hash of the input using the SHA-256 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 32 bytes.
expression: The expression to use to compute the hash.
Computes the hash of the input using the SHA-512 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 64 bytes.
expression: The expression to use to compute the hash.
Returns the current date as of the start of the query.
Returns the current time as of the start of the query.
Returns the current timestamp as of the start of the query.
Returns the current time zone in the format defined by IANA (e.g., America/Los_Angeles) or as fixed offset from UTC (e.g., +08:35)
Parses the ISO 8601 formatted str into a timestamp with time zone.
str: Any column or literal expression.
Parses the ISO 8601 formatted str into a date.
str: Any column or literal expression.
Returns the UNIX timestamp unixtime as a timestamp with time zone using str for the time zone.
unixtime: A UNIX timestamp.
str: A timezone.
Returns the UNIX timestamp unixtime as a timestamp with time zone using hours and minutes for the time zone offset.
unixtime: A UNIX timestamp.
hours: Hours portion of timezone offset.
minutes: Minutes portion of timezone offset.
Returns the current time as of the start of the query.
Returns the current timestamp as of the start of the query.
This is an alias for current_timestamp.
Formats expression as an ISO 8601 string. expression can be date, timestamp, or timestamp with time zone.
expression: A date or timestamp expression.
Returns timestamp as a UNIX timestamp.
timestamp: A timestamp value.
Truncates the date to the specified granularity.
date: The date to truncate.
date_part: The date part. Supported values are: DAY, WEEK, ISOWEEK, MONTH, QUARTER, YEAR, ISOYEAR.
Adds an interval value of type unit to timestamp. Subtraction can be performed by using a negative value.
unit: The unit to add.
value: The interval value.
timestamp: A timestamp expression.
Computes the number of specified date_part differences between two date expressions. This can be thought of as the number of date_part boundaries crossed between the two dates. If the first date occurs before the second date, then the result is negative.
date1: The first date.
date2: The second date.
date_part: The date part. Supported values are: DAY, MONTH, QUARTER, YEAR.
Formats the date_expr according to the specified format_string.
date_expr: The date to format.
format_string: The format string used to format the date_expr.
Uses a format_string and a string representation of a date to return a DATE object.
date_string: The date string to parse.
format_string: The format string used to parse the date_string.
Formats date_expr as a string using format_string.
date_expr: The date to format.
format_string: The format string used to format the date_expr.
Parses string into a timestamp with time zone using format.
date_string: The date string to parse.
format_string: The format string used to parse the date_string.
Returns the day of the month of a TIMESTAMP data type as an integer between 1 and 31, inclusively.
timestamp: The timestamp from which to return the day of the month.
Returns the day of the month of a TIMESTAMP data type as an integer between 1 and 31, inclusively.
timestamp: The timestamp from which to return the day of the month.
Returns the day of the week of a TIMESTAMP data type as an integer between 1 (Sunday) and 7 (Saturday), inclusively.
timestamp: The timestamp from which to return the day of the week.
Returns the day of the year of a TIMESTAMP data type as an integer between 1 and 366, inclusively. The integer 1 refers to January 1.
timestamp: The timestamp from which to return the day of the year.
Alias for DAY_OF_WEEK.
timestamp: The timestamp from which to return the day of the week.
Alias for DAY_OF_YEAR.
timestamp: The timestamp from which to return the day of the year.
Returns the hour of a TIMESTAMP data type as an integer between 0 and 23, inclusively.
timestamp: The timestamp from which to return the hour as an integer.
Returns the minutes of a TIMESTAMP data type as an integer between 0 and 59, inclusively.
timestamp: The timestamp from which to return the minutes as an integer.
Returns the month of a TIMESTAMP data type as an integer between 1 and 12, inclusively.
timestamp: The timestamp from which to return the month as an integer.
Returns the quarter of the year of a TIMESTAMP data type as an integer between 1 and 4, inclusively.
timestamp: The timestamp from which to return the quarter as an integer.
Returns the seconds of a TIMESTAMP data type as an integer between 0 and 59, inclusively. During a leap second, the integer range is between 0 and 60, inclusively.
timestamp: The timestamp from which to return the second as an integer.
Returns the week of a TIMESTAMP data type as an integer between 1 and 53, inclusively. Weeks begin on Sunday, so if January 1 is on a day other than Sunday, week 1 has fewer than 7 days and the first Sunday of the year is the first day of week 2.
timestamp: The timestamp from which to return the week as an integer.
Alias for WEEK function.
timestamp: The timestamp from which to return the week as an integer.
Returns the year of a TIMESTAMP data type.
timestamp: The timestamp from which to return the year as an integer.
Returns the year of the ISO week from timestamp.
timestamp: The timestamp from which to return the year as an integer.
Alias for YEAR_OF_WEEK.
timestamp: The timestamp from which to return the year as an integer.
Returns the portion of str that matches the capturing group within the regular expression.
str: The string to match in the regular expression.
reg_exp: The regular expression to match.
Returns an array of all substrings of value that match the regular expression, regex. The REGEXP_EXTRACT_ALL function only returns non-overlapping matches. For example, using this function to extract ana from banana returns only one substring, not two.
str: The string to match in the regular expression.
reg_exp: The regular expression to match.
Evaluates the regular expression pattern and determines if it is contained within orig_str. This function is similar to the LIKE operator, except that the pattern only needs to be contained within orig_str, rather than needing to match all of string. In other words, this performs a contains operation rather than a match operation. You can match the entire string by anchoring the pattern using ^ and $: SELECT regexp_like('1a 2b 14m', '\d+b'); -- true
orig_str: The original string to match in the regular expression.
reg_exp: The regular expression to match.
Returns a string where any substring of orig_str that matches reg_exp is replaced with replace_str. For example, REGEXP_REPLACE ('Hello', 'lo', 'p') returns Help.
orig_str: The original string to match in the regular expression.
reg_exp: The regular expression to match.
replace_str: The replacement for the matched orig_str in the regular expression.
Determine if value exists in json (a string containing a JSON array).
json: The JSON to search.
value: The value to search for.
Returns the element at the specified index into the json_array. The index is zero-based.
json: The JSON to select a value from.
index: The index to retrieve within the array.
Returns the array length of json (a string containing a JSON array).
json: The JSON to calculate the length of.
Selects a value in json according to the JSONPath expression json_path. json_path must be a string constant. Returns the value in JSON string format.
json: The JSON to select a value from.
json_path: The JSON path of the value contained in json.
Selects a value in json according to the JSONPath expression json_path. json_path must be a string constant, and bracket notation is not supported. Returns a scalar JSON value.
json: The JSON to select a value from.
json_path: The JSON path of the value contained in json.
Returns json as a string
json: The JSON to format.
Parse string as a json.
json: The JSON string to parse.
Like json_extract(), but returns the size of the value. For objects or arrays, the size is the number of members, and the size of a scalar value is zero.
json: A JSON expression.
json_path: The JSON path of the value contained in json.
Returns an array created from the input expression elements.
expression: Any column or literal expression.
Returns the average on non-null values. Each distinct value of expression is aggregated only once into the result.
expression: The expression to use to compute the average.
Returns the approximate number of distinct input values. This function provides an approximation of count(DISTINCT x). Zero is returned if all input values are null. This function should produce a standard error of no more than e, which is the standard deviation of the (approximately normal) error distribution over all possible sets. It does not guarantee an upper bound on the error for any specific input set. The current implementation of this function requires that e be in the range: [0.01150, 0.26000].
expression: The expression to perform the approximate count distinct on.
e: The error distribution.
Returns the Pearson correlation coefficient of a set of number pairs.
numeric_expression1: The first series.
numeric_expression2: The second series.
Computes the population covariance of the values computed by numeric_expression1 and numeric_expression2.
numeric_expression1: The first series.
numeric_expression2: The second series.
Computes the sample covariance of the values computed by numeric_expression1 and numeric_expression2.
numeric_expression1: The first series.
numeric_expression2: The second series.
Returns the standard deviation of the values computed by numeric_expr. Rows with a NULL value are not included in the calculation.
numeric_expression: The series to calculate STDDEV on.
Computes the population standard deviation of the value computed by numeric_expr.
numeric_expression: The series to calculate STDDEV on.
Returns the standard deviation of the values computed by numeric_expr. Rows with a NULL value are not included in the calculation.
numeric_expression: The series to calculate STDDEV_SAMP on.
Alias for VAR_SAMP.
numeric_expression: The series to calculate VARIANCE on.
Alias for VAR_POP.
numeric_expression: The series to calculate VARIANCE on.
Computes the population variance of the values computed by numeric_expr.
numeric_expression: The series to calculate VARIANCE on.
Computes the sample variance of the values computed by numeric_expr.
numeric_expression: The series to calculate VARIANCE on.
Returns the largest of the provided values.
value1: The first value to compare.
value2: The second value to compare.
Returns the smallest of the provided values.
value1: The first value to compare.
value2: The second value to compare.
Returns the value of the first non-null expression. The remaining expressions are not evaluated. All input expressions must be implicitly coercible to a common supertype.
expr1: Any expression
expr2: Any expression
Returns NULL if expression = expression_to_match is true, otherwise returns expression. expression and expression_to_match must be implicitly coercible to a common supertype; equality comparison is done on coerced values.
expression: Any expression
expression_to_match: Any expression to be matched
Cast is used in a query to indicate that the result type of an expression should be converted to some other type.
expression: The expression to cast.
type: The type to cast the expression to.
Like CAST, but returns NULL if the cast fails.
expression: The expression to cast.
type: The type to cast the expression to.
Returns the name of the type of the provided expression
expr: An expression.
Returns the absolute value of the argument.
expression: Any column or literal expression.
Returns the cube root of expression.
expression: Any column or literal expression.
Rounds the argument up to the nearest whole number and returns the rounded value.
expression: Any column or literal expression.
Synonym for CEIL function.
expression: Any column or literal expression.
Returns expression, converted from radians to degrees.
expression: Any column or literal expression.
Returns the result of raising the constant "e" - the base of the natural logarithm - to the power of expression.
expression: Any column or literal expression.
Rounds the argument down to the nearest whole number and returns the rounded value.
expression: Any column or literal expression.
Returns the value of string interpreted as a base-radix number.
string: A string column or literal expression.
radix: An optional radix.
Returns the natural logarithm of the argument.
expression: Any column or literal expression.
Returns the natural logarithm of the argument.
expression: Any column or literal expression.
base: The base to use when performing the logarithm.
Returns the Base-2 logarithm of the argument.
expression: Any column or literal expression.
Returns the Base-10 logarithm of the argument.
expression: Any column or literal expression.
Returns the modulus (remainder) of n divided by m.
n: Any column or literal expression.
m: Any column or literal expression.
Returns PI.
Returns the result of raising expression1 to the power of expression2.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
Synonym of POW function.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
Returns expression, converted from degrees to radians.
expression: Any column or literal expression.
Returns a pseudo-random number in the range 0.0 >= value < expression. If expression is not specified, the value is 1.0.
expression: Any column or literal expression.
Rounds the argument either up or down to the nearest whole number (or if specified, to the specified number of digits) and returns the rounded value.
expression: Any column or literal expression.
integer_digits: The number of digits to round to.
Returns the signum function of expression, that is: 0 if the argument is 0, 1 if the argument is greater than 0, -1 if the argument is less than 0.
expression: Any column or literal expression.
Returns the square root of the expression. Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
Returns the base-radix representation of expression.
expression: Any column or literal expression.
radix: An optional radix.
Returns expression rounded to integer by dropping digits after decimal point.
expression: Any column or literal expression.
Returns the arc cosine of the argument.
expression: Any column or literal expression.
Returns arcsine in radians.
expression: Any column or literal expression.
Returns arc tangent of the argument.
expression: Any column or literal expression.
Returns the arc tangent of the two arguments.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
Returns the cosine of the argument.
expression: Any column or literal expression.
Returns the hyperbolic cosine of the argument.
expression: Any column or literal expression.
Returns the sine of the argument.
expression: Any column or literal expression.
Returns the tangent of the argument.
expression: Any column or literal expression.
Returns the hyperbolic tangent of the argument.
expression: Any column or literal expression.
Returns the bitwise AND of x and y in 2's complement representation.
x: A numeric expression to perform the bitwise operation.
y: A numeric expression to perform the bitwise operation.
Count the number of bits set in expression (treated as bits-bit signed integer) in 2's complement representation.
expression: The expression to perform the bit count operation on.
bits: The number of bits.
Returns the bitwise NOT of x in 2's complement representation.
x: A numeric expression to perform the bitwise operation.
Returns the bitwise OR of x and y in 2's complement representation.
x: A numeric expression to perform the bitwise operation.
y: A numeric expression to perform the bitwise operation.
Returns the bitwise XOR of x and y in 2's complement representation.
x: A numeric expression to perform the bitwise operation.
y: A numeric expression to perform the bitwise operation.
Returns the Unicode code point n as a single character string.
n: Unicode code point value.
Returns the Unicode code point of the only character of expression.
expression: Any column or literal expression.
Returns the concatenation of two or more strings, or NULL if any of the values are NULL.
str1: The first string to concatenate.
str2: The second string to concatenate.
str3: The third string to concatenate.
Returns a numerical value for the length of the string. Example: if str is '123456', LENGTH returns 6.
str: The string to calculate the length on.
Returns the original string with all characters in lower case.
str: The string to lower.
Pads str1 on the left with str2, repeating str2 until the result string is exactly numeric_expr characters. Example: LPAD('1', 7, '?') returns ??????1.
str1: The string to pad.
numeric_expression: The number of str2 instances to pad.
str2: The pad characters.
Removes characters from the left side of str1.
str1: The string to trim.
Replaces all instances of from_value within original_value with to_value.
original_value: The string to search in.
from_value: The string to search for.
to_value: The string to replace instances of from_value.
Returns the reverse of the input STRING or BYTES.
str: The string to reverse.
Pads str1 on the right with str2, repeating str2 until the result string is exactly numeric_expr characters. Example: RPAD('1', 7, '?') returns 1??????.
str1: The string to pad.
numeric_expression: The number of str2 instances to pad.
str2: The pad characters.
Removes trailing characters from the right side of str1.
str: The string to trim.
Splits a string into repeated substrings. If delimiter is specified, the SPLIT function breaks str into substrings, using delimiter as the delimiter.
str: The string to split.
delimiter: The delimiter to split the string on. Default delimiter is a comma (,).
limit: The maximum number of values to return.
Splits str on delimiter and returns the field index. Field indexes start with 1. If the index is larger than than the number of fields, then null is returned.
str: The string to split.
delimiter: The delimiter to split the string on. Default delimiter is a comma (,).
index: The index to return.
Returns the 1-based index of the first occurrence of value2 inside value1. Returns 0 if value2 is not found.
str1: The string to search in.
str2: The string to search for.
Returns a substring of str, starting at index. If the optional max_len parameter is used, the returned string is a maximum of max_len characters long. Counting starts at 1, so the first character in the string is in position 1 (not zero). If index is 5, the substring begins with the 5th character from the left in str. If index is -4, the substring begins with the 4th character from the right in str. Example: SUBSTR('awesome', -4, 4) returns the substring some.
str: The original string.
index: The starting index.
max_len: The maximum length of the return string.
Removes leading and trailing whitespace from str.
str: The string to trim.
Returns the original string with all characters in upper case.
str: The string to upper.
Encodes str into a UTF-8 varbinary representation.
str: The string to convert to UTF-8
Decodes a UTF-8 encoded string from binary. Invalid UTF-8 sequences are replaced with replace. The replacement string replace must either be a single character or empty (in which case invalid characters are removed).
expression:
replace: The string to replace invalid UTF-8 sequences.
Converts a sequence of BYTES into a base64-encoded STRING. To convert a base64-encoded STRING into BYTES, use FROM_BASE64.
string_expr: The string to convert to base64 encoding.
Converts the base64-encoded input string_expr into BYTES format. To convert BYTES to a base64-encoded STRING, use TO_BASE64.
string_expr: The string to convert from base64 encoding.
Encodes binary into a base64 string_expr representation using the URL safe alphabet.
string_expr: The string to convert from base64 encoding.
Decodes binary data from the base64 encoded string_expr using the URL safe alphabet.
string_expr: The string to convert from base64 encoding.
Converts a sequence of BYTES into a hexadecimal STRING. Converts each byte in the STRING as two hexadecimal characters in the range (0..9, a..f). To convert a hexadecimal-encoded STRING to BYTES, use FROM_HEX.
string_expr: The string to convert to hexadecimal encoding.
Converts a hexadecimal-encoded STRING into BYTES format. Returns an error if the input STRING contains characters outside the range (0..9, A..F, a..f). The lettercase of the characters does not matter. To convert BYTES to a hexadecimal-encoded STRING, use TO_HEX.
string_expr: The string to convert from hexadecimal encoding.
Computes the hash of the input using the MD5 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 16 bytes.
expression: The expression to use to compute the hash.
Computes the hash of the input using the SHA-1 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 20 bytes.
expression: The expression to use to compute the hash.
Computes the hash of the input using the SHA-256 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 32 bytes.
expression: The expression to use to compute the hash.
Computes the hash of the input using the SHA-512 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 64 bytes.
expression: The expression to use to compute the hash.
Returns the current date as of the start of the query.
Returns the current time as of the start of the query.
Returns the current timestamp as of the start of the query.
Returns the current time zone in the format defined by IANA (e.g., America/Los_Angeles) or as fixed offset from UTC (e.g., +08:35)
Parses the ISO 8601 formatted str into a timestamp with time zone.
str: Any column or literal expression.
Parses the ISO 8601 formatted str into a date.
str: Any column or literal expression.
Returns the UNIX timestamp unixtime as a timestamp with time zone using str for the time zone.
unixtime: A UNIX timestamp.
str: A timezone.
Returns the UNIX timestamp unixtime as a timestamp with time zone using hours and minutes for the time zone offset.
unixtime: A UNIX timestamp.
hours: Hours portion of timezone offset.
minutes: Minutes portion of timezone offset.
Returns the current time as of the start of the query.
Returns the current timestamp as of the start of the query.
This is an alias for current_timestamp.
Formats expression as an ISO 8601 string. expression can be date, timestamp, or timestamp with time zone.
expression: A date or timestamp expression.
Returns timestamp as a UNIX timestamp.
timestamp: A timestamp value.
Truncates the date to the specified granularity.
date: The date to truncate.
date_part: The date part. Supported values are: DAY, WEEK, ISOWEEK, MONTH, QUARTER, YEAR, ISOYEAR.
Adds an interval value of type unit to timestamp. Subtraction can be performed by using a negative value.
unit: The unit to add.
value: The interval value.
timestamp: A timestamp expression.
Computes the number of specified date_part differences between two date expressions. This can be thought of as the number of date_part boundaries crossed between the two dates. If the first date occurs before the second date, then the result is negative.
date1: The first date.
date2: The second date.
date_part: The date part. Supported values are: DAY, MONTH, QUARTER, YEAR.
Formats the date_expr according to the specified format_string.
date_expr: The date to format.
format_string: The format string used to format the date_expr.
Uses a format_string and a string representation of a date to return a DATE object.
date_string: The date string to parse.
format_string: The format string used to parse the date_string.
Formats date_expr as a string using format_string.
date_expr: The date to format.
format_string: The format string used to format the date_expr.
Parses string into a timestamp with time zone using format.
date_string: The date string to parse.
format_string: The format string used to parse the date_string.
Returns the day of the month of a TIMESTAMP data type as an integer between 1 and 31, inclusively.
timestamp: The timestamp from which to return the day of the month.
Returns the day of the month of a TIMESTAMP data type as an integer between 1 and 31, inclusively.
timestamp: The timestamp from which to return the day of the month.
Returns the day of the week of a TIMESTAMP data type as an integer between 1 (Sunday) and 7 (Saturday), inclusively.
timestamp: The timestamp from which to return the day of the week.
Returns the day of the year of a TIMESTAMP data type as an integer between 1 and 366, inclusively. The integer 1 refers to January 1.
timestamp: The timestamp from which to return the day of the year.
Alias for DAY_OF_WEEK.
timestamp: The timestamp from which to return the day of the week.
Alias for DAY_OF_YEAR.
timestamp: The timestamp from which to return the day of the year.
Returns the hour of a TIMESTAMP data type as an integer between 0 and 23, inclusively.
timestamp: The timestamp from which to return the hour as an integer.
Returns the minutes of a TIMESTAMP data type as an integer between 0 and 59, inclusively.
timestamp: The timestamp from which to return the minutes as an integer.
Returns the month of a TIMESTAMP data type as an integer between 1 and 12, inclusively.
timestamp: The timestamp from which to return the month as an integer.
Returns the quarter of the year of a TIMESTAMP data type as an integer between 1 and 4, inclusively.
timestamp: The timestamp from which to return the quarter as an integer.
Returns the seconds of a TIMESTAMP data type as an integer between 0 and 59, inclusively. During a leap second, the integer range is between 0 and 60, inclusively.
timestamp: The timestamp from which to return the second as an integer.
Returns the week of a TIMESTAMP data type as an integer between 1 and 53, inclusively. Weeks begin on Sunday, so if January 1 is on a day other than Sunday, week 1 has fewer than 7 days and the first Sunday of the year is the first day of week 2.
timestamp: The timestamp from which to return the week as an integer.
Alias for WEEK function.
timestamp: The timestamp from which to return the week as an integer.
Returns the year of a TIMESTAMP data type.
timestamp: The timestamp from which to return the year as an integer.
Returns the year of the ISO week from timestamp.
timestamp: The timestamp from which to return the year as an integer.
Alias for YEAR_OF_WEEK.
timestamp: The timestamp from which to return the year as an integer.
Returns the portion of str that matches the capturing group within the regular expression.
str: The string to match in the regular expression.
reg_exp: The regular expression to match.
Returns an array of all substrings of value that match the regular expression, regex. The REGEXP_EXTRACT_ALL function only returns non-overlapping matches. For example, using this function to extract ana from banana returns only one substring, not two.
str: The string to match in the regular expression.
reg_exp: The regular expression to match.
Evaluates the regular expression pattern and determines if it is contained within orig_str. This function is similar to the LIKE operator, except that the pattern only needs to be contained within orig_str, rather than needing to match all of string. In other words, this performs a contains operation rather than a match operation. You can match the entire string by anchoring the pattern using ^ and $: SELECT regexp_like('1a 2b 14m', '\d+b'); -- true
orig_str: The original string to match in the regular expression.
reg_exp: The regular expression to match.
Returns a string where any substring of orig_str that matches reg_exp is replaced with replace_str. For example, REGEXP_REPLACE ('Hello', 'lo', 'p') returns Help.
orig_str: The original string to match in the regular expression.
reg_exp: The regular expression to match.
replace_str: The replacement for the matched orig_str in the regular expression.
Determine if value exists in json (a string containing a JSON array).
json: The JSON to search.
value: The value to search for.
Returns the element at the specified index into the json_array. The index is zero-based.
json: The JSON to select a value from.
index: The index to retrieve within the array.
Returns the array length of json (a string containing a JSON array).
json: The JSON to calculate the length of.
Selects a value in json according to the JSONPath expression json_path. json_path must be a string constant. Returns the value in JSON string format.
json: The JSON to select a value from.
json_path: The JSON path of the value contained in json.
Selects a value in json according to the JSONPath expression json_path. json_path must be a string constant, and bracket notation is not supported. Returns a scalar JSON value.
json: The JSON to select a value from.
json_path: The JSON path of the value contained in json.
Returns json as a string
json: The JSON to format.
Parse string as a json.
json: The JSON string to parse.
Like json_extract(), but returns the size of the value. For objects or arrays, the size is the number of members, and the size of a scalar value is zero.
json: A JSON expression.
json_path: The JSON path of the value contained in json.
Returns an array created from the input expression elements.
expression: Any column or literal expression.
Returns the average on non-null values. Each distinct value of expression is aggregated only once into the result.
expression: The expression to use to compute the average.
Returns the approximate number of distinct input values. This function provides an approximation of count(DISTINCT x). Zero is returned if all input values are null. This function should produce a standard error of no more than e, which is the standard deviation of the (approximately normal) error distribution over all possible sets. It does not guarantee an upper bound on the error for any specific input set. The current implementation of this function requires that e be in the range: [0.01150, 0.26000].
expression: The expression to perform the approximate count distinct on.
e: The error distribution.
Returns the Pearson correlation coefficient of a set of number pairs.
numeric_expression1: The first series.
numeric_expression2: The second series.
Computes the population covariance of the values computed by numeric_expression1 and numeric_expression2.
numeric_expression1: The first series.
numeric_expression2: The second series.
Computes the sample covariance of the values computed by numeric_expression1 and numeric_expression2.
numeric_expression1: The first series.
numeric_expression2: The second series.
Returns the standard deviation of the values computed by numeric_expr. Rows with a NULL value are not included in the calculation.
numeric_expression: The series to calculate STDDEV on.
Computes the population standard deviation of the value computed by numeric_expr.
numeric_expression: The series to calculate STDDEV on.
Returns the standard deviation of the values computed by numeric_expr. Rows with a NULL value are not included in the calculation.
numeric_expression: The series to calculate STDDEV_SAMP on.
Alias for VAR_SAMP.
numeric_expression: The series to calculate VARIANCE on.
Alias for VAR_POP.
numeric_expression: The series to calculate VARIANCE on.
Computes the population variance of the values computed by numeric_expr.
numeric_expression: The series to calculate VARIANCE on.
Computes the sample variance of the values computed by numeric_expr.
numeric_expression: The series to calculate VARIANCE on.
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
The Provider for LDAP supports standard SQL joins like the following examples.
An inner join selects only rows from both tables that match the join condition:
A left join selects all rows in the FROM table and only matching rows in the JOIN table:
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.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
The Provider for Microsoft Active Directory supports standard SQL joins like the following examples.
An inner join selects only rows from both tables that match the join condition:
A left join selects all rows in the FROM table and only matching rows in the JOIN table:
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.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
The Provider for JSON supports standard SQL joins like the following examples.
An inner join selects only rows from both tables that match the join condition:
A left join selects all rows in the FROM table and only matching rows in the JOIN table:
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.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
SELECT {
[ TOP <numeric_literal> | DISTINCT ]
{
*
| {
<expression> [ [ AS ] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM <table_reference> [ [ AS ] <identifier> ]
} [ , ... ]
[ [
INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }
] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]
] [ ... ]
[ WHERE <search_condition> ]
[ GROUP BY <column_reference> [ , ... ]
[ HAVING <search_condition> ]
[ UNION [ ALL ] <select_statement> ]
[
ORDER BY
<column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
]
[
LIMIT <expression>
[
{ OFFSET | , }
<expression>
]
]
} | SCOPE_IDENTITY()
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { [ DISTINCT ] <expression> } )
| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )
| NULLIF ( <expression> , <expression> )
| COALESCE ( <expression> , ... )
| CASE <expression>
WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
[ ELSE { <expression> | NULL } ]
END
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]
} [ { AND | OR } ... ]
SELECT * FROM [AwsDataCatalog].[sampledb].Customers
SELECT [TotalDue] AS MY_TotalDue FROM [AwsDataCatalog].[sampledb].Customers
SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM [AwsDataCatalog].[sampledb].Customers
SELECT * FROM [AwsDataCatalog].[sampledb].Customers WHERE CustomerId = '12345'
SELECT * FROM [AwsDataCatalog].[sampledb].Customers WHERE CustomerId = '12345';
SELECT COUNT(*) AS MyCount FROM [AwsDataCatalog].[sampledb].Customers
SELECT COUNT(DISTINCT TotalDue) FROM [AwsDataCatalog].[sampledb].Customers
SELECT DISTINCT TotalDue FROM [AwsDataCatalog].[sampledb].Customers
SELECT TotalDue, MAX(AnnualRevenue) FROM [AwsDataCatalog].[sampledb].Customers GROUP BY TotalDue
SELECT Orders.OrderDate, Customers.ContactName FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
SELECT Name, TotalDue FROM [AwsDataCatalog].[sampledb].Customers ORDER BY TotalDue ASC
SELECT Name, TotalDue FROM [AwsDataCatalog].[sampledb].Customers LIMIT 10
SELECT * FROM [AwsDataCatalog].[sampledb].Customers WHERE CustomerId = @param
SELECT Orders.OrderDate, Customers.ContactNameFROM CustomersINNER JOIN OrdersON Customers.CustomerID=Orders.CustomerID
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
The Provider for XML supports standard SQL joins like the following examples.
An inner join selects only rows from both tables that match the join condition:
A left join selects all rows in the FROM table and only matching rows in the JOIN table:
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.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
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:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
The CData ADO.NET Provider for OData supports standard SQL joins like the following examples.
An inner join selects only rows from both tables that match the join condition:
A left join selects all rows in the FROM table and only matching rows in the JOIN table:
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.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
Returns the value rounded up to the nearest whole number (no decimal component).
expression: The value to round.
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.
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.
Returns the current date using the specified datetime_offset.
datetime_offset: The datetime offset to use when retrieving the current date.
Returns the integer that specifies the day component of the specified date.
datetime_date: The datetime string that specifies the date.
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.
Returns the value rounded down to the nearest whole number (no decimal component).
value: The value to round.
Returns the decimal value that specifies the fractional seconds component of the specified time.
datetime_time: The datetime string that specifies the time.
Returns the integer that specifies the hour component of the specified time.
datetime_time: The datetime string that specifies the time.
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.
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.
Returns the number of characters of the specified string expression.
string_expression: The string expression.
Returns the latest possible datetime.
Returns the earliest possible datetime.
Returns the integer that specifies the minute component of the specified time.
datetime_time: The datetime string that specifies the time.
Returns the integer that specifies the month component of the specified date.
datetime_date: The datetime string that specifies the date.
Returns the current datetime.
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.
Returns the value to the nearest whole number (no decimal component).
value: The value to round.
Returns the integer that specifies the second component of the specified time.
datetime_time: The datetime string that specifies the time.
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.
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.
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.
Returns the current time using datetime_offset.
datetime_offset: The datetime offset.
Returns the string_expression with the uppercase character data converted to lowercase.
string_expression: The string expression to lowercase.
Returns the integer that specifies the offset minutes component of the specified date.
datetime_date: The datetime string that specifies the date.
Returns the duration value in total seconds.
string_duration: The duration.
Returns the string_expression with the lowercase character data converted to uppercase.
string_expression: The string expression to uppercase.
Returns the string_expression with the leading and trailing whitespace removed.
string_expression: The string expression to trim.
Returns the integer that specifies the year component of the specified date.
datetime_date: The datetime string that specifies the date.
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below belowfor details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
The Provider for RSS supports standard SQL joins like the following examples.
An inner join selects only rows from both tables that match the join condition:
A left join selects all rows in the FROM table and only matching rows in the JOIN table:
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.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
Other properties (Proxy...) are detailed in section common properties
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
The Provider for Microsoft Dataverse supports standard SQL joins like the following examples.
An inner join selects only rows from both tables that match the join condition:
A left join selects all rows in the FROM table and only matching rows in the JOIN table:
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.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
The Provider for REST supports standard SQL joins like the following examples.
An inner join selects only rows from both tables that match the join condition:
A left join selects all rows in the FROM table and only matching rows in the JOIN table:
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.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
SELECT {
[ TOP <numeric_literal> | DISTINCT ]
{
*
| {
<expression> [ [ AS ] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM <table_reference> [ [ AS ] <identifier> ]
} [ , ... ]
[ [
INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }
] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]
] [ ... ]
[ WHERE <search_condition> ]
[ GROUP BY <column_reference> [ , ... ]
[ HAVING <search_condition> ]
[ UNION [ ALL ] <select_statement> ]
[
ORDER BY
<column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
]
[
LIMIT <expression>
[
{ OFFSET | , }
<expression>
]
]
} | SCOPE_IDENTITY()
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { [ DISTINCT ] <expression> } )
| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )
| NULLIF ( <expression> , <expression> )
| COALESCE ( <expression> , ... )
| CASE <expression>
WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
[ ELSE { <expression> | NULL } ]
END
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]
} [ { AND | OR } ... ]
SELECT * FROM NorthwindOData
SELECT [Username] AS MY_Username FROM NorthwindOData
SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM NorthwindOData
SELECT * FROM NorthwindOData WHERE Email = 'ana.trujilo@northwind.org'
SELECT COUNT(*) AS MyCount FROM NorthwindOData
SELECT COUNT(DISTINCT Username) FROM NorthwindOData
SELECT DISTINCT Username FROM NorthwindOData
SELECT Username, MAX(AnnualRevenue) FROM NorthwindOData GROUP BY Username
SELECT [people].[personal.age] AS age, [people].[personal.gender] AS gender, [people].[personal.name.first] AS first_name, [people].[personal.name.last] AS last_name, [vehicles].[model],FROM [people], [vehicles]WHERE [people].[_id] = [vehicles].[people_id]
SELECT Email, Username FROM NorthwindOData ORDER BY Username ASC
SELECT Email, Username FROM NorthwindOData LIMIT 10
SELECT * FROM NorthwindOData WHERE Email = @param
SELECT COUNT(*) FROM NorthwindOData WHERE Email = 'ana.trujilo@northwind.org'
SELECT COUNT(DISTINCT Email) AS DistinctValues FROM NorthwindOData WHERE Email = 'ana.trujilo@northwind.org'
SELECT Username, AVG(AnnualRevenue) FROM NorthwindOData WHERE Email = 'ana.trujilo@northwind.org' GROUP BY Username
SELECT MIN(AnnualRevenue), Username FROM NorthwindOData WHERE Email = 'ana.trujilo@northwind.org' GROUP BY Username
SELECT Username, MAX(AnnualRevenue) FROM NorthwindOData WHERE Email = 'ana.trujilo@northwind.org' GROUP BY Username
SELECT SUM(AnnualRevenue) FROM NorthwindOData WHERE Email = 'ana.trujilo@northwind.org'
SELECT [people].[personal.age] AS age, [people].[personal.gender] AS gender, [people].[personal.name.first] AS first_name, [people].[personal.name.last] AS last_name, [vehicles].[model],FROM [people], [vehicles]WHERE [people].[_id] = [vehicles].[people_id]
SELECT [people].[personal.age] AS age, [people].[personal.gender] AS gender, [people].[personal.name.first] AS first_name, [people].[personal.name.last] AS last_name, [vehicles].[model],FROM [people]LEFT OUTER JOIN [vehicles]ON [people].[_id] = [vehicles].[people_id]
SELECT * FROM MyTable WHERE MyDateField = L_TODAY()
SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()
SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)
SELECT {
[ TOP <numeric_literal> | DISTINCT ]
{
*
| {
<expression> [ [ AS ] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM <table_reference> [ [ AS ] <identifier> ]
} [ , ... ]
[ [
INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }
] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]
] [ ... ]
[ WHERE <search_condition> ]
[ GROUP BY <column_reference> [ , ... ]
[ HAVING <search_condition> ]
[ UNION [ ALL ] <select_statement> ]
[
ORDER BY
<column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
]
[
LIMIT <expression>
[
{ OFFSET | , }
<expression>
]
]
} | SCOPE_IDENTITY()
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { [ DISTINCT ] <expression> } )
| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )
| NULLIF ( <expression> , <expression> )
| COALESCE ( <expression> , ... )
| CASE <expression>
WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
[ ELSE { <expression> | NULL } ]
END
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]
} [ { AND | OR } ... ]
SELECT * FROM Lead
SELECT [FullName] AS MY_FullName FROM Lead
SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Lead
SELECT * FROM Lead WHERE FirstName <> 'Bartholomew'
SELECT COUNT(*) AS MyCount FROM Lead
SELECT COUNT(DISTINCT FullName) FROM Lead
SELECT DISTINCT FullName FROM Lead
SELECT FullName, MAX(AnnualRevenue) FROM Lead GROUP BY FullName
SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId
SELECT Id, FullName FROM Lead ORDER BY FullName ASC
SELECT Id, FullName FROM Lead LIMIT 10
SELECT * FROM Lead WHERE FirstName = @param
SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId
SELECT Customers.ContactName, Orders.OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerId=Orders.CustomerId
SELECT * FROM MyTable WHERE MyDateField = L_TODAY()
SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()
SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)
SELECT {
[ TOP <numeric_literal> | DISTINCT ]
{
*
| {
<expression> [ [ AS ] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM <table_reference> [ [ AS ] <identifier> ]
} [ , ... ]
[ [
INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }
] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]
] [ ... ]
[ WHERE <search_condition> ]
[ GROUP BY <column_reference> [ , ... ]
[ HAVING <search_condition> ]
[ UNION [ ALL ] <select_statement> ]
[
ORDER BY
<column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
]
[
LIMIT <expression>
[
{ OFFSET | , }
<expression>
]
]
} | SCOPE_IDENTITY()
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { [ DISTINCT ] <expression> } )
| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )
| NULLIF ( <expression> , <expression> )
| COALESCE ( <expression> , ... )
| CASE <expression>
WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
[ ELSE { <expression> | NULL } ]
END
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]
} [ { AND | OR } ... ]
SELECT * FROM RSSFeed
SELECT [Author] AS MY_Author FROM RSSFeed
SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM RSSFeed
SELECT * FROM RSSFeed WHERE Title = 'US'
SELECT COUNT(*) AS MyCount FROM RSSFeed
SELECT COUNT(DISTINCT Author) FROM RSSFeed
SELECT DISTINCT Author FROM RSSFeed
SELECT Author, MAX(AnnualRevenue) FROM RSSFeed GROUP BY Author
SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId
SELECT Guid, Author FROM RSSFeed ORDER BY Author ASC
SELECT Guid, Author FROM RSSFeed LIMIT 10
SELECT * FROM RSSFeed WHERE Title = @param
SELECT COUNT(*) FROM RSSFeed WHERE Title = 'US'
SELECT COUNT(DISTINCT Guid) AS DistinctValues FROM RSSFeed WHERE Title = 'US'
SELECT Author, AVG(AnnualRevenue) FROM RSSFeed WHERE Title = 'US' GROUP BY Author
SELECT MIN(AnnualRevenue), Author FROM RSSFeed WHERE Title = 'US' GROUP BY Author
SELECT Author, MAX(AnnualRevenue) FROM RSSFeed WHERE Title = 'US' GROUP BY Author
SELECT SUM(AnnualRevenue) FROM RSSFeed WHERE Title = 'US'
SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId
SELECT Customers.ContactName, Orders.OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerId=Orders.CustomerId
SELECT * FROM MyTable WHERE MyDateField = L_TODAY()
SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()
SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)
SELECT {
[ TOP <numeric_literal> | DISTINCT ]
{
*
| {
<expression> [ [ AS ] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM <table_reference> [ [ AS ] <identifier> ]
} [ , ... ]
[ [
INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }
] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]
] [ ... ]
[ WHERE <search_condition> ]
[ GROUP BY <column_reference> [ , ... ]
[ HAVING <search_condition> ]
[ UNION [ ALL ] <select_statement> ]
[
ORDER BY
<column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
]
[
LIMIT <expression>
[
{ OFFSET | , }
<expression>
]
]
} | SCOPE_IDENTITY()
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { [ DISTINCT ] <expression> } )
| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )
| NULLIF ( <expression> , <expression> )
| COALESCE ( <expression> , ... )
| CASE <expression>
WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
[ ELSE { <expression> | NULL } ]
END
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]
} [ { AND | OR } ... ]
SELECT * FROM Accounts
SELECT [Name] AS MY_Name FROM Accounts
SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Accounts
SELECT * FROM Accounts WHERE Name <> 'MyAccount'
SELECT COUNT(*) AS MyCount FROM Accounts
SELECT COUNT(DISTINCT Name) FROM Accounts
SELECT DISTINCT Name FROM Accounts
SELECT Name, MAX(AnnualRevenue) FROM Accounts GROUP BY Name
SELECT Accounts.Name, Contracts.ContractNumber FROM Accounts, Contracts WHERE Accounts.AccountId=Contracts._AccountId_Value
SELECT accountid, Name FROM Accounts ORDER BY Name ASC
SELECT accountid, Name FROM Accounts LIMIT 10
SELECT * FROM Accounts WHERE Name = @param
SELECT COUNT(*) FROM Accounts WHERE Name = 'MyAccount'
SELECT COUNT(DISTINCT accountid) AS DistinctValues FROM Accounts WHERE Name <> 'MyAccount'
SELECT Name, AVG(AnnualRevenue) FROM Accounts WHERE Name <> 'MyAccount' GROUP BY Name
SELECT MIN(AnnualRevenue), Name FROM Accounts WHERE Name <> 'MyAccount' GROUP BY Name
SELECT Name, MAX(AnnualRevenue) FROM Accounts WHERE Name <> 'MyAccount' GROUP BY Name
SELECT SUM(AnnualRevenue) FROM Accounts WHERE Name = 'MyAccount'
SELECT Accounts.Name, Contracts.ContractNumber FROM Accounts, Contracts WHERE Accounts.AccountId=Contracts._AccountId_Value
SELECT Accounts.Name, Contracts.ContractNumber FROM Accounts LEFT OUTER JOIN Contracts ON Accounts.AccountId=Contracts._AccountId_Value
SELECT * FROM MyTable WHERE MyDateField = L_TODAY()
SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()
SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|