|
This article explains nothing. It merely shows what SQL constructs and functions may be used within <%AG %>. Actually training the reader in SQL is very much beyond the scope of this manual. If you're not fully conversant in SQL, we highly recommend the reference texts listed in the Queries article. |
Every query in AggreGate Query Language has the following syntax:
selectStatement
SELECT [ALL | DISTINCT]
{ selectExpression | tableAlias.fieldReference | tableAlias.* | * } [, ...]
FROM tableList
[WHERE expression]
[GROUP BY expression [, ...]]
[HAVING expression]
[{ UNION [ALL] | MINUS | INTERSECT } selectStatement]
[ORDER BY orderExpression) [, ...]]
[LIMIT <limit> [OFFSET <offset>]]
|
The LIMIT clause may be used only if all Context References listed in FROM clause have aliases. (I.e. "SELECT ... FROM context_reference AS alias LIMIT n, m") |
tableList
table [{CROSS | INNER | LEFT OUTER | RIGHT OUTER} JOIN table ON expression] [, ...]
table
{ (selectStatement) [AS tableAlias] | contextReference [AS tableAlias]}
contextMask { :contextEntityReference [: ...] }
contextEntityReference
{ contextVariableName | contextVariableGroupName.* | contextFunctionName ( contextFunctionParameters ) }
{ null | 'value' | "value"} [, ...]
orderExpression
{ [tableAlias.]fieldReference | selectExpression } [ASC | DESC]
selectExpression
{ expression | COUNT(*) | { COUNT | MIN | MAX | SUM | AVG } ([ALL | DISTINCT] expression) } [[AS] label]
expression
[NOT] condition [{ OR | AND } condition]
condition
{
value [|| value]
| value { = | < | <= | > | >= | <> | != } value
| value IS [NOT] NULL
| EXISTS(selectStatement)
| value BETWEEN value AND value
| value [NOT] IN ( {value [, ...] | selectStatement } )
| value [NOT] LIKE value [ESCAPE] value
}
value
[+ | -] { term [{ + | - | * | / | || } term]
| ( condition )
| function ( [parameter] [,...] )
| selectStatement giving one value
| {ANY|ALL} (selectStatement giving single column)
term
{ 'string' | number | floatingpoint | [tableAlias.]fieldReference | TRUE | FALSE | NULL }
{ { contextVariableName | contextFunctionName} $ dataTableFieldName | writebackFieldName }
writebackFieldName
"CONTEXT_ID" | "PARENT_ID" | "RECORD_INDEX"
Numerical built-in Functions
ABS(d)
Returns the absolute value of a double value
ACOS(d)
Returns the arc cosine of an angle
ASIN(d)
Returns the arc sine of an angle
ATAN(d)
Returns the arc tangent of an angle
ATAN2(a,b)
Returns the tangent of a/b
BITAND(a,b)
Return a & b
BITOR(a,b)
Returns a | b
CEILING(d)
Returns the smallest integer that is not less than d
COS(d)
Returns the cosine of an angle
COT(d)
Returns the cotangent of an angle
DEGREES(d)
Converts radians to degrees
EXP(d)
Returns e (2.718...) raised to the power of d
FLOOR(d)
Returns the largest integer that is not greater than d
LOG(d)
Returns the natural logarithm (base e)
LOG10(d)
Returns the logarithm (base 10)
MOD(a,b)
Returns a modulo b
PI()
Returns pi (3.1415...)
POWER(a,b)
Returns a raised to the power of b
RADIANS(d)
Converts degrees to radians
RAND()
Returns a random number x bigger or equal to 0.0 and smaller than 1.0
ROUND(a,b)
Rounds a to b digits after the decimal point
ROUNDMAGIC(d)
Solves rounding problems such as 3.11-3.1-0.01
SIGN(d)
Returns -1 if d is smaller than 0, 0 if d==0 and 1 if d is bigger than 0
SIN(d)
Returns the sine of an angle
SQRT(d)
Returns the square root
TAN(A)
Returns the trigonometric tangent of an angle
TRUNCATE(a,b)
Truncates a to b digits after the decimal point
String built-in Functions
ASCII(s)
Returns the ASCII code of the leftmost character of s
BIT_LENGTH(str)
Returns the length of the string in bits
CHAR(c)
Returns a character that has the ASCII code c
CHAR_LENGTH(str)
Returns the length of the string in characters
CONCAT(str1,str2)
Returns str1 + str2
DIFFERENCE(s1,s2)
Returns the difference between the sound of s1 and s2
HEXTORAW(s1)
Returns translated string
INSERT(s,start,len,s2)
Returns a string where len number of characters beginning at start has been replaced by s2
LCASE(s)
Converts s to lower case
LEFT(s,count)
Returns the leftmost count of characters of s) - requires double quoting - use SUBSTRING() instead
LENGTH(s)
Returns the number of characters in s
LOCATE(search,s[,start])
Returns the first index (1=left, 0=not found) where search is found in s, starting at start
LTRIM(s)
Removes all leading blanks in s
OCTET_LENGTH(str)
Returns the length of the string in bytes (twice the number of characters)
RAWTOHEX(s1)
Returns translated string
REPEAT(s,count)
Returns s repeated count times
REPLACE(s,replace,s2)
Replaces all occurrences of replace in s with s2
RIGHT(s,count)
Returns the rightmost count of characters of s
RTRIM(s)
Removes all trailing spaces
SOUNDEX(s)
Returns a four character code representing the sound of s
SPACE(count)
Returns a string consisting of count spaces
SUBSTR(s,start[,len])
Alias for substring
SUBSTRING(s,start[,len])
Returns the substring starting at start (1=left) with length len
UCASE(s)
Converts s to upper case
LOWER(s)
Converts s to lower case
UPPER(s)
Converts s to upper case
Date/Time built-in Functions
CURDATE()
Returns the current date
CURTIME()
Returns the current time
DATEDIFF(string, datetime1, datetime2)
Returns the count of units of time elapsed from datetime1 to datetime2. The string indicates the unit of time and can have the following values 'ms'='millisecond', 'ss'='second','mi'='minute','hh'='hour', 'dd'='day', 'mm'='month', 'yy' = 'year'. Both the long and short form of the strings can be used.
DAYNAME(date)
Returns the name of the day
DAYOFMONTH(date)
Returns the day of the month (1-31)
DAYOFWEEK(date)
Returns the day of the week (1 means Sunday)
DAYOFYEAR(date)
Returns the day of the year (1-366)
HOUR(time)
Return the hour (0-23)
MINUTE(time)
Returns the minute (0-59)
MONTH(date)
Returns the month (1-12)
MONTHNAME(date)
Returns the name of the month
NOW()
Returns the current date and time as a timestamp) - use CURRENT_TIMESTAMP instead
QUARTER(date)
Returns the quarter (1-4)
SECOND(time)
Returns the second (0-59)
WEEK(date)
Returns the week of this year (1-53)
YEAR(date)
Returns the year
System built-in Functions
CASEWHEN(exp,v1,v2)
If exp is true, v1 is returned, else v2
COALESCE(expr1,expr2,expr3,...)
If expr1 is not null then it is returned else, expr2 is evaluated and if not null it is returned and so on
NULLIF(v1,v2)
If v1 equals v2 return null, otherwise v1