### Arithmetic Operators

SQL Arithmetic Operators are used to make arithmetic return a single value.

Operator | Description | Example
--- | --- | ---
+ | Addition operator | amount + 2
- | Minus operator | amount - 2
* | Multiplication operator | amount * 2
/ | Division operator | amount / 2
% | Modulo operator | amount % 2
DIV | Integer division | amount DIV 2
- | Unary minus. It changes the sign of the argument. | -(amount)

### Aggregate Functions

SQL Aggregate Functions returns a single value, calculated from values in a column.

Function | Description | Example
--- | --- | ---
AVG(columnName) | Returns the average value | AVG(amount)
COUNT(columnName) | Returns the number of rows | COUNT(id)
FIRST(columnName) | Returns the first value | FIRST(name)
LAST(columnName) | Returns the last value | LAST(name)
MAX(columnName) | Returns the largest value | MAX(date_entered)
MIN(columnName) | Returns the smallest value | MIN(date_entered)
SUM(columnName) | Returns the sum | SUM(amount)

### DateTime Functions

SQL DateTime Functions returns a date/time value, based on the input value.

Function | Description | Example
--- | --- | ---
NOW() | Returns the current system date and time | NOW()
CURDATE() | Returns the current date | CURDATE()
CURTIME() | Returns the current time | CURTIME()
DATE(dateString) | Extracts the date part of a datetime expression | DATE('2010-01-01 18:00:00')
EXTRACT(unit* FROM dateA) | Returns a single unit part of a datetime | EXTRACT(MONTH FROM date_entered)
DATE_ADD(dateA, INTERVAL time unit*) | Adds a specified time interval to a date | DATE_ADD(date_entered, INTERVAL 4 WEEK)
DATE_SUB(dateA, INTERVAL time unit*) | Subtracts a specified time interval from a date | DATE_SUB(date_entered, INTERVAL 2 DAY)
DATEDIFF(dateA, dateB) | Returns the number of days between two dates | DATEDIFF(date_entered, '2010-01-01')
DATE_FORMAT(dateA, format*) | Displays datetime data in different formats | DATE_FORMAT(date_entered, '%d-%m-%Y')

### Numeric Functions

SQL Integer Functions returns a single value, based on the numeric input value.

Function | Description | Example
--- | --- | ---
BIN(intA) | Returns binary value for IntA | BIN(9)
CHAR(numA,... [USING charset]) | Returns the character value of the given integer values | CHAR(0xC3BC USING utf8)
CONV(numA, fromBase, toBase) | Converts numbers between different bases | CONV(8, 10, 2)
ELT(intA, str1, str2, str3,...) | Returns str1 if intA=1 and str2 if intA=2 and so on | SELECT(2, 'a', 'b', 'c')
HEX(intA) | Returns hexadecimal representation for intA | HEX(amount)
OCT(intA) | Returns octal representation for intA | OCT(15)
ROUND(numA, decimals) | Rounds a numeric field to the number of decimals specified | ROUND(3.14159, 4)
FORMAT(numA, decimals) | Formats the number according to the template '#,###.##', Rounding decimal | FORMAT(amount, 2)

### String Functions

SQL String Functions returns a single string value, based on the input value.

Function | Description | Example
--- | --- | ---
BIT_LENGTH(strA) | Returns the String length in bits | BIT_LENGTH('a')
CHAR_LENGTH(strA) | Returns the length of a text field | CHAR_LENGTH(name)
CHARACTER_LENGTH(strA) | Returns the length of a text field | CHARACTER_LENGTH('abcdefg')
LENGTH/OCTET_LENGTH(strA) | Returns the length of a text field in bytes | LENGTH('AlineaSol')
CHARINDEX(strA, strB [,start]) | Returns the starting position of a character strA in StringB | CHARINDEX('Sol', 'AlineaSol')
ASCII(strA) | Returns the ASCII code value of a keyboard char button | ASCII('A')
UCASE/UPPER(strA) | Converts a field to upper case | UCASE('alineasol')
LCASE/LOWER(strA) | Converts a field to lower case | LCASE(name)
MID(strA, start[, length]) | Extract characters from a text field | MID(description, 5, 10)
CONCAT(strA, strB,....) | Returns concatenated Strings | CONCAT('Alinea', 'Sol')

### String Functions 2

SQL String Functions returns a single string value, based on the input value.

Function | Description | Example
--- | --- | ---
CONCAT_WS(separator, strA, strB,....) | Returns concatenated Strings with separator char | CONCAT_WS(' ', name, surName)
SPACE(int) | Returns spaces in your SQL query | SPACE(2)
REPLACE(strA, strB, strC) | Replaces all occurrences of the strB in the strA with strC | REPLACE(description, 'a', 'z')
INSERT(strA, pos, len, newString) | Replaces into strA, newStr String | INSERT(name, 0, 1, 'Asol')
INSTR(strA,subStr) | Return the position of first occurrence of subStr in strA | INSTR(description, 'AlineaSol')
LEFT(strA, int) | Returns left part of strA with int number of characters | LEFT(name, 5)
RIGHT(strA, int) | Returns right part of strA with int number of characters | RIGHT('AlineaSol', 3)
SUBSTRING(strA, startIndex, length) | Returns part of a strA | SUBSTRING(name, 0, 10)
REVERSE(strA) | Returns reverse of strA | REVERSE(name)
LTRIM(strA) | Returns strA after removing leading blanks on Left side | LTRIM(' Z')
RTRIM(strA) | Returns strA after removing leading blanks on Right side | RTRIM('A ')

### Appendix

mySQL Used Constants

DateTime Units | DateTime Format
--- | ---
MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH | %a Abbreviated weekday name, %b Abbreviated month name %c Month, numeric, %D Day of month with English suffix %d Day of month, numeric (00-31), %e Day of month, numeric (0-31) %f Microseconds, %H Hour (00-23) %h Hour (01-12), %I Hour (01-12) %i Minutes, numeric (00-59), %j Day of year (001-366) %k Hour (0-23), %l Hour (1-12) %M Month name, %m Month, numeric (00-12) %p AM or PM, %r Time, 12-hour (hh:mm:ss AM or PM) %S Seconds (00-59), %s Seconds (00-59) %T Time, 24-hour (hh:mm:ss) %U Week (00-53) where Sunday is the first day of week %u Week (00-53) where Monday is the first day of week %V Week (01-53) where Sunday is the first day of week, used with %X %v Week (01-53) where Monday is the first day of week, used with %x %W Weekday name, %w Day of the week (0=Sunday, 6=Saturday) %X Year of the week where Sunday is the first day of week, four digits, used with %V %x Year of the week where Monday is the first day of week, four digits, used with %v %Y Year, four digits, %y Year, two digits

### Variables

Variables Definition Used by AlineaSol Reports.
The variables over related and/or custom fields can only be used if at least one field of that related/custom table is added to the report.

Variabe Type | Description | Example
--- | --- | ---
${this} | References the value of the current field | ${this}
${bean->Field} | References a field of the current report module | ${bean->name}
${bean_cstm->Field_c} | References a custom field of the current report module | ${bean_cstm->account_type_c}
${ModName->LinkField->RelatedField} | References a related field value | ${Accounts->account_id_c->name}
${ModName_Cstm->LinkField->RelatedField_c} | References a related custom field value | ${Accounts_Cstm->account_id_c->account_type_c}