Index...
Centrallix Documentation
|
6.8 Functions and Operators
Operators
Here is a summary of the operators that Centrallix supports. Note that these operators apply to both SQL expressions and the expressions found in application, component, and report definitions.
| Operator | Description |
|---|
| p + q | Addition/concatenation. For numeric values (integer, double, and money data types), this simply adds the two values together and results in a similar value. For string values, this concatenates the two strings together. This operator is undefined for datetime values. |
| p - q | Subtraction. Subtracts one integer, double, or money data type from another. This operator is undefined for datetime values. For strings, this is the reverse of concatenation: "abc" - "c" would yield "ab". Subtracting strings in this way can be particularly useful when a concatenation expression requires separators between elements, but not after the last element, as in: sum(:string_field + ',') - ',' |
| p * q | Multiplication. Multiplies one integer, double, or money data type by another. This operator is also defined for multiplying a string value by an integer value - the result is the repeated concatenation of the string (p) a number (q) times. |
| p / q | Division. Divides one integer, double, or money data type by another. This operator is not defined for strings and datetime values. |
| NOT p | Boolean negation. Boolean values in Centrallix are treated as integers; this operator returns 0 if p is not 0, and returns 1 if p is 0. Alternatively, returns FALSE if p is TRUE, and returns TRUE if p is FALSE. |
| p AND q | Boolean AND. Returns TRUE (1) if both p and q are not FALSE (0). Otherwise returns FALSE (0). |
| p OR q | Boolean OR. Returns TRUE (1) if either p or q is not FALSE (0). Otherwise returns FALSE (0). |
| p IS NULL | Returns TRUE (1) if p is NULL, otherwise returns FALSE. |
| p IS NOT NULL | Returns TRUE (1) if p isn't NULL, otherwise returns FALSE. |
| p = q | Equality comparison. Also can be written as "p == q". Results in TRUE (1) if p and q have the same value. If either p or q is NULL, this returns FALSE (0). Even if both p and q are NULL, this returns FALSE (0). |
| p != q | Inequality comparison. Also can be written as "p <> q". Results in TRUE (1) only if p and q have different values. If either p or q are NULL (or if both are NULL), results in FALSE(0). |
| p > q | Greater-than. Results in TRUE (1) only if p is greater than q and both p and q are not NULL. When comparing strings, the strings are compared on a binary basis. |
| p >= q | Greater than or equals. Also can be written "p !< q" (p is not less than q). Results in TRUE only if p is greater than or equal to q and both p and q are not NULL. |
| p < q | Less-than. Results in TRUE (1) only if p is less than q and both p and q are not NULL. |
| p <= q | Less than or equals. Also can be written "p !> q" (p is not greater than q). Results in TRUE only if p is less than or equal to q and both p and q are not NULL. |
| :p | Not truly an operator, but :p means that p is an attribute name, and the operation should use the value of the attribute p in the "current" object (depends on context) |
| :p:q | Not truly an operator, but :p:q means that q is an attribute of the object p. |
| ::p | Not truly an operator, but ::p means that p is an attribute, and the operation should use the value of the attribute p in the "parent" object (depends on context) |
Functions
Here is a summary of the functions that Centrallix supports. Again, note that these functions are used both in the SQL language and in expressions present in applications, components, reports, etc. Not all functions are appropriate in all contexts.
Most functions return NULL if any of the required arguments is NULL. There are some exceptions to this, however.
| Function | Description |
|---|
| abs(x) | Takes the absolute value of x. abs(x) is always positive, zero, or NULL (if x is NULL). Not defined for strings or datetime values. |
| ascii(s) | Returns the ASCII or unicode value of the first character of string s |
| avg(x) | An aggregate function - see next section. |
| charindex(sn,sh) | Locates the position of the first occurrence of needle string sn inside haystack string sh. The position is a 1-based integer (1 refers to the first position in sh). Returns 0 if sn does not occur anywhere in sh. |
| char_length(s) | Returns the number of characters in the string s, or NULL if s is NULL. |
| condition(c,a,b) | If c is TRUE (not 0), returns a, otherwise returns b. a and b can be any data type, but c must evaluate to an integer/boolean value. |
| convert(t,v) | Converts the value v to the data type indicated by t. |
| count(x) | An aggregate function - see next section. |
| dateadd(p,i,d) | Adds a time interval to the given datetime value d. p can be "year", "month", "day", "hour", "minute", or "second". i is the number of years/months/etc to add to the datetime value d (or, if negative, to subtract from the datetime value d). |
| datepart(p,d) | Returns one integer part of a datetime value d. p can be "year", "month", "day", "hour", "minute", or "second". Year values are always 4-digit. Month and Day values always start at 1; Hour, Minute, and Second values always start at 0. Hours are returned in 24-hour format (0 = midnight, 23 = 11pm). |
| escape(s,e,b) | Escapes characters in the string s. Any characters occurring in s that are listed in e will be prefixed by a backslash (\). Always escapes the backslash, whether or not it is in the string e. If any characters in b (the blacklist) occur in s, an error will result. |
| eval(s) | Treats s as if it were an expression and evaluates it. Please use caution with this function - if misused, it could allow a user to evaluate arbitrary expressions in the context of the server, which could compromise security under certain circumstances and configurations. |
| first(x) | An aggregate function - see next section. |
| getdate() | Returns the current date/time value. |
| isnull(v,d) | Returns v if v is not NULL. If the value v is NULL, returns the default value d instead. |
| last(x) | An aggregate function - see next section. |
| lower(s) | Converts the string s to all lowercase. |
| ltrim(s) | Removes spaces from the left hand side (beginning) of the string s. |
| lztrim(s) | Removes leading zeros from a numeric value when that number is represented in the string s. |
| max(x) | An aggregate function - see next section. |
| min(x) | An aggregate function - see next section. |
| quote(s) | Encloses the string s in double quote marks, and escapes (with a backslash) any double quote marks or backslashes that occur inside of s. |
| ralign(s,n) | Pads the string s on the left hand side with spaces until it reaches the size n; thus, the string s is aligned on the right. |
| replicate(s,n) | Returns n copies of s concatenated back to back. n can be at most 255. |
| right(s,n) | Returns the rightmost n characters in s. |
| round(n,d) | Rounds the value n to d decimal places (to the right of the decimal point). If d is zero, rounds n to a whole number. If d is negative, rounds to (-d) digits to the left of the decimal point (example: d = -1 means round to the nearest 10). |
| rtrim(s) | Removes spaces from the right hand side of s. Note that rtrim() is done automatically for certain types of data when it comes from a database. For example, fixed-length char() values are automatically trimmed. |
| runclient(v) | A domain declaration. See section below. |
| runserver(v) | A domain declaration. See section below. |
| runstatic(v) | A domain declaration. See section below. |
| substring(s,p,n) | Returns the part of string s which starts at position p (1 is the first position) and continues for n characters. n is optional - if omitted, substring() returns the entire string beginning at position p. |
| sum(x) | An aggregate function - see next section. |
| upper(s) | Uppercases the string s. |
| user_name() | Returns the name of the currently logged in user. |
| wordify(n) | Converts the number n into a human-readable string notation using words. For example, wordify(4) = "Four", wordify(25) = "Twenty-Five", and wordify($1.23) = "One And 23/100". The returned string is always in mixed case; it can be converted to all-uppercase or all-lowercase using upper() or lower(). |
Aggregate Functions
Aggregate functions are used in queries, often in combination with a GROUP BY clause. These functions operate on multiple values from multiple records.
| Function | Description |
|---|
| avg(x) | Computes the average of all values x. If no values were averaged, this returns NULL (since an average of no values is indefinite) |
| count(x) | Returns the number of non-NULL values x. If no values were counted, this function returns 0. It does not count NULL values. To cause this function to count all rows in a SQL query, regardless of NULLs, then pass a constant value to count(), as in count(1). count(1) is the same as count(*) in other SQL languages. |
| first(x) | Returns the first value x encountered. Note that in most cases, the order of values returned from a SQL query can not be guaranteed, so the meaningfulness of first() is highly dependent on the context of its use. This is sometimes useful along with ORDER BY to select the first value of x occurring in rows sorted by a different field. |
| last(x) | Returns the last value x encountered. See discussion for first(). |
| max(x) | Returns the highest value x encountered. If no values were found (or all were NULL), this function returns NULL. |
| min(x) | Returns the smallest value x encountered. |
| sum(x) | Returns the sum of all values x that were encountered. If no values were seen (or all were NULL), sum() returns NULL. This function, when x represents a string value, will return the concatenation of non-NULL values of x. |
Domain Declaration Pseudo-Functions
A domain declaration provides control over the context in which an expression (or part thereof) will be evaluated.
| Function | Description |
|---|
| runclient(v) | Indicates that the expression v should be evaluated in the context of a running application, using real-time values where possible. |
| runserver(v) | Indicates that the expression v should be evaluated in the context of server-side data, including parameters passed to components and applications, or data resulting from a SQL query while a report is being generated. Data on the client side, such as the content of an editbox, are not available to a runserver() expression. |
| runstatic(v) | This is the default and does not need to be explicitly used. It means that none of the values in the expression v depend on any server-side or client-side context. Such expressions consist generally of constant values. For example, an application designer may choose to do arithmetic for widget layout using runstatic() expressions instead of manually adding or subtracting values. |
Comments...
(none yet)
Add a Comment...
|
