General Reference
Chapter 1 Expressions, Operators, and Built-in Functions
This page defines the operators and functions you can use to write SilverStream expressions. It contains the following sections:
For background information, see the chapter on SilverStream expressions in the Programmer's Guide.
For information about building expressions, see the chapter on the Expression Builder in the online Tools Guide.
Data types 
This section summarizes the data types supported by the Silverstream Expression Builder. Note that for integer, floating point, and boolean literals, SilverStream uses Locale.US formatting.
Integer literals
Integer literals can be int and long, and all integer types are signed. The data type assigned to the integer literal depends on its value. For example the integer 3L has a long integer value of 3. You must precede negative integers with a minus sign.
Floating-point literals
Floating-point literals have an integer part and a decimal part, for example 3.141592. They result in a floating-point number that has a data type of double, regardless of the number's precision. Append the letter f or F to the number to force it to the float data type.
To include exponents in your floating-point literal, append the letter e or E to the number, for example 20e35. You can also indicate that the exponent is a negative number by including the minus sign (-) along with the letter e, for example 10e-5.
Boolean literals
There are two boolean literals: true and false. Use these words anywhere you have a test. You can also use them as values for initializing boolean variables.
String literals 
Strings are composed of characters. Java takes string literals and turns them into objects (instances of the String class). String literals are surrounded by single or double quotes and can contain character constants such as newline and tab characters. The following is an example of a string literal with a tab character:
"Now is the winter \t of our discontent"
Date/Time literals 
Date literals are composed of numbers and characters. You must enter a date in ANSI SQL format in order for it to be parsed correctly.
You must enclose date/time/timestamp literals in either single or double quotes. For example:
`1998-06-12'
NOTE The default timezone format is GMT
Operators 
This section lists the different kinds of operators that are available in the Expression Builder.
Arithmetic operators 
Arithmetic operators perform mathematical operations on two or more numeric operands. For example:
employees.salary + (employees.salary * employees.per_centRaise)
The following is a list of supported operators.
Operator
|
Description
|
Usage
|
+
|
add
|
operand_1 + operand_2
|
-
|
subtract
|
operand_1 - operand_2
|
*
|
Multiply
|
operand_1 * operand_2
|
/
|
Divide
|
operand_1 / operand_2
|
^
|
Exponent
|
operand_1 ^ operand_2
|
%
|
Modulus
|
operand_1 % operand_2
|
Relational operators 
Relational operators do one of the following:
-
Compare two values (or operands) and return the result as a boolean.
-
Compare a single value to an expression or set of expressions and return a boolean.
The following is the list of relational operators supported by SilverStream.
Operator
|
Description
|
Usage
|
Example
|
=
|
equal
|
operand_1=operand_2
|
employeeid = 5
(Like "==" in Java)
|
<>
|
Not equal
|
operand_1<>operand_2
|
employeeid <> 10
|
<
|
Less than
|
operand_1<operand_2
|
employeeid < 10
|
>
|
greater than
|
operand_1 > operand_2
|
employeeid > 2
|
<=
|
less than or equal to
|
operand_1<=operand_2
|
employeeid <= 5
|
>=
|
Greater than or equal to
|
operand_1>=operand_2
|
employeeid >= 5
|
in
|
in
|
operand in (expression1, expression2, ...)
|
Example using the in operator
|
not in
|
not in
|
operand not in (expression1, expression2, ..)
|
Example using the in operator
|
between
|
between
|
Operand between expression1 and expression2
|
Example using the between operator
|
not between
|
not between
|
Operand not between expression1 and expression2
|
Example using the between operator
|
Example using the in operator
This operator verifies that a single value is included in a list of expressions. The syntax is:
operand in (expression1, expression2, ...)
In this example, a string is tested against two columns in a table:
`cancelled' in (products.ordered, products.available)
In this example, if integer value in the column products.available equals 8, the following expression will evaluate to true:
products.productid in (6, 3, 4*2, 9)
Example using the between operator
This operator verifies that a single value falls within a range of two specified values. The syntax is:
operand between expression1, expression2
In this example, if employees.level equals "F", the following expression evaluates to false:
employees.level between "A" and "D"
Here is an example using the not between operator:
price.wholesale not between price.retail and price.discount
Logical operators 
Use logical operators to combine two or more Boolean expressions. The following lists the logical operators supported by SilverStream.
Operator
|
Usage
|
Example
|
and
|
(expression_1 )and (expression_2)
|
(employees.employeeid > 2) and (employees.employeeid < 10)
|
or
|
(expression_1 )or (expression_2)
|
(employees.employeeid between 1 and 10) or (employees.employeeid = 12)
|
not
|
not (expression)
|
not (employees.employeeid > 10 or (employees.empoyeeid < 5 )
|
String operators 
String operators allow you to combine and compare text strings for various type of expressions such as queries and validation expressions. The following table lists the string operators supported by SilverStream.
Operator
|
Usage
|
Description
|
+ concatenate
|
(expression_1) + (expression_2)
|
This operator works exactly like the Java "+" operator. At least one of the expressions must be a string.
Example using the concatenate operator
|
like
|
operand like char%
|
Matches a specific pattern. % is a wildcard character meaning `match any characters'
Examples using the like operator
|
startsWith
|
operand startsWith string
|
True, if the operand starts with string.
|
endsWith
|
operand endsWith string
|
True, if operand ends with the specified string.
|
equalsIgnoreCase
|
operand_1 equalsIgnoreCase operand_2
|
Tests strings for equality, ignoring case.
Example using the equalsIgnoreCase operator
|
not like
|
string not like char%
|
Does not match a specific pattern. % Is the wildcard character.
|
not startsWith
|
operand does not startsWith string.
|
Does not start with the specified string.
|
not endsWith
|
operand not endsWith string
|
True, if operand does not end with the specified string.
|
not equalsIgnoreCase
|
operand_1 not equalsIgnoreCase operand_2
|
Tests strings for equality, including case.
|
Example using the concatenate operator
The string "+" operator allows you to concatenate expressions to a string output as long as at least one of the expressions is a string literal. (This is identical to the corresponding operator in Java.) In this example:
Employees.firstname + "is "+ Employees.age + "years old."
might result in "John is 34 years old".
Examples using the like operator
This string operator allows you to search for a matching pattern in a column. The like operator is valid for CHAR and VARCHAR columns, and has the following syntax:
expression [not] like 'match_string' [`ESCAPE escape_character']
-
expression is any column or expression with a CHAR or VARCHAR data type.
-
Match_string is a string. It may include wildcard characters (see below).
-
escape_character specifies the character that you are using as an escape character within the match string to indicate that wildcard characters should be interpreted as literals.
SilverStream provides the following wildcard characters that you can use in LIKE clauses to match character strings.
-
% matches a string of characters
-
_ matches a single character
The column data can be matched to constants, variables, or other columns that contain these wildcard symbols. Enclose the match strings and character strings in quotation marks. The following table shows some examples of wildcard characters:
Example
|
Result
|
customers.company_name like `C%'
|
Selects all companies that begin with the letter `C'.
|
like '%ER'
|
Selects names that end with the characters "ER" (Royer, Voyager, Gouner).
|
like 'Green%'
|
Selects names that begin with the fivecharacters ''Green'' (Green, Greensboro, Greenstein).
|
like '100+%' ESCAPE '+'
|
uses the character `+' as an escape character for the wildcard `%', and selects the four characters that match `100%'.
|
Example using the equalsIgnoreCase operator
This operator allows you to compare strings for equality, not considering the case of the characters. For example, the following expression evaluates to true.
"DAVID" equalsIgnoreCase "david"
This example uses not equalsIgnoreCase to test two column values:
employees.firstname not equalsIgnoreCase employees.nickname
Other operators 
SilverStream provides other operators that can be used for different types of queries. These operators are listed under "Other" in the Expression Builder.
Operator
|
Syntax
|
Description
|
if Then
|
if (expression1) then (expression2) else (expression3) endif
|
Creates a conditional query. If the statement that follows the If keyword evaluates to true, the statement following the keyword then executes. If the value of the if statement is false, the statement following the else keyword executes.
Example using the if then operator
|
fullTextSearch
|
table name fullTextSearch string
|
Creates a full text search query against a table.
Examples using the fullTextSearch operator
|
isnull
|
named_variable isnull
|
Tests if a value is null and returns a boolean. Isnull can only be used with named variables, not expressions.
Example using the isnull operator
|
not isnull
|
named_variable not isnull
|
Test if a value is not null and returns a boolean. Can only be used with named variables, not expressions.
Example using the isnull operator
|
userin
|
userin ("group1, group2, ..")
|
Tests if a user belongs to a group defined on the server.
Example using the userin operator
|
Example using the if then operator
Use this operator to write conditional expressions, or to nest a series of expressions. The syntax is as follows:
if (expression) then
(expression) else (expression) endif
The following example shows a nested conditional expression. It could be used, for example, to test a value in a text field in order to set the text for a corresponding label.
if( fld_securityLevel = "1" ) then
"Top Secret Clearance"
else
if( fld_securityLevel = "2" ) then
"Secret Clearance"
else
"Uknown Security Clearance"
endif
endif
This example shows a similar operation that tests a table column (of type int) rather than a text field value.
if( employees.securityLevel = 1 ) then
"Top Secret Clearance"
else
if( employees.securityLevel = 2 ) then
"Secret Clearance"
else
"Uknown Security Clearance"
endif
endif
Examples using the fullTextSearch operator
Use this operator to specify a full text search against a table. note that expressions using this operator are passed directly to the full text search engine, which has its own expression language. for more information, refer to the Full Text Search section in the help system.
Use the following syntax to specify a single search string:
tablename fullTextSearch "`clause'"
If you want to specify more than one string using logical operators, enclose the whole full text search string in double quotes, and enclose each search clause in single quotes. The following table shows some examples.
Example
|
Result
|
orders fullTextSearch "'*wrench'"
|
Finds the orders that contain the word "wrench" preceded by any characters.
|
orders fullTextSearch "'hammer' & 'nail'"
|
Finds the orders that contain the word "hammer" and the word "nail"
|
orders fullTextSearch "'hammer ' | 'nail'"
|
Finds the orders that contain the word "hammer" or the word "nail"
|
orders fullTextSearch "('hammer' & 'wrench') ~('sledge' | 'monkey')
|
finds the orders that contain the word "hammer" and the word "wrench" but not "sledge" or "monkey"
|
Example using the userin operator
Use this operator to test if a user is in a group or groups defined on the Silverstream Server. The syntax for this operator is a follows:
userin ("list of string expressions ")
The following two example use the userid() security function with userin() to determine if the currently logged in user is defined in a group defined on an NT server:
userid() userin (groupid("MyNTServer\\Developers")
For more information, see
Security functions.
Example using the isnull operator
When a column contains an unknown value, it is referred to as having a null value. This is different from having a value of blank or 0; null has no value. Null values are not considered greater than, less than, or equal to any other value, including another null.
You can use the isnull operator to search for null values in a column (or all values except null values). The isnull expression returns either true or false. It has the following syntax:
column_name isnull
For example, to find all products where the price is unknown:
prod_price isnull
and to find all products where the price is known:
prod_price not isnull
All comparisons with null are false except isnull. This means that a column containing a null value would not match either of the following expressions:
column<=3
column>=3
Operator precedence 
Operator precedence determines the order in which operations are performed. SilverStream handles operator precedence in the same way that Java does. In the following example, the division operator (/) has a higher precedence than the addition operator (+), so the division calculation is performed before the addition.
1+2/4 //results in 1.5
you can override precedence by using parentheses:
(1+2)/4 //results in .75
The following table shows the operator precedence for evaluations from top to bottom.
Order
|
Operator
|
Description
|
Type
|
1
|
and
|
Conditional AND
|
boolean
|
1
|
or
|
Conditional OR
|
boolean
|
2
|
<<, >>, <<=, >>=
|
Relational (any data type) (LT, GT,LE, GE)
|
boolean
|
2
|
= , !=
|
Equality and inequality of value (EQ, NE)
|
boolean
|
3
|
not
|
Conditional not
|
boolean
|
4
|
in
|
Operand in (set_of_expressions)
|
boolean
|
4
|
userin
|
Expression that evaluates to true or false, if user is part of a group listed in `list_of_strings'
|
boolean
|
4
|
like
|
Matches a specific pattern. % is a wildcard character.
|
boolean
|
4
|
between
|
True, if within range of expressions inclusive.
|
boolean
|
4
|
fullTextSearch
|
creates a full text search query against a table.
|
boolean
|
4
|
startsWith
|
True, if the operand starts with the specified string.
|
boolean
|
4
|
equalsIgnorecase
|
True, if the operands match, ignoring case.
|
boolean
|
4
|
endsWith
|
True, if the operand ends with the specified string.
|
boolean
|
4
|
isnull
|
Checks to see if expression is null.
|
boolean
|
5
|
+
|
Addition (ADD)
|
arithmetic
|
5
|
-
|
Subtraction (SUBTRACT)
|
arithmetic
|
6
|
/
|
Division (DIVIDE)
|
arithmetic
|
6
|
*
|
Multiplication (MULTIPLY)
|
arithmetic
|
6
|
%
|
remainder (REMAIN)
|
arithmetic
|
7
|
^
|
Exponent (EXPON)
|
arithmetic
|
Built-in functions 
This section describes the functions that SilverStream supports in the Expression Builder.
Numeric functions 
Numeric functions performs various types of arithmetic calculations and comparisons on a range of numeric types.Numeric functions take any number value and always return a double. The following is a list of supported numeric functions.
Name
|
Syntax
|
Description
|
Example
|
abs
|
abs(numeric_expr )
|
Returns the absolute value of numeric_expr.
If it is negative, then the result is -numeric_expr ; otherwise, the result is numeric_expr .
|
abs (-30) returns 30
|
acos
|
acos(double_expr )
|
Returns the arc-cosine (inverse of cosine) of x, in the range from 0 to Pi (-1 to 1)
|
acos (-1) returns 3.141
|
asin
|
asin(double_expr )
|
Returns the arc-sine (inverse of sine) of double_expr , in radians, in the range `[-pi/2..pi/2]' or `[0..pi]', respectively. Returns an error if double_expr is out of range `[-1.0..1.0]'.
|
asin (1) returns 1.570
|
atan
|
atan(double_expr1 [,double_expr2 ])
|
Returns the arc-tangent (inverse tangent) of y , in radians, in the range `[-pi/2..pi/2]' . If x is not provided, or if `double_expr1/ double_expr2'is not in the `[-pi..pi]' range an error is returned.
|
atan (2) returns 1.107
|
atan2
|
atan2(double_expr1, double_expr2 )
|
Returns the arctangent of double_expr1 / double_expr2 in radians.
|
atan2(5, 6) returns 0.719
|
ceil
|
ceiling(double_expr )
|
Returns the smallest integer not less than double_expr, as a double number.
|
ceil (9.87) returns 10
|
cos
|
cos(double_expr )
|
Returns the cosine of the angle.
|
cos (3.141) returns -1
|
exp
|
exp(double_expr )
|
Returns e to the power of double_expr .
|
exp (4) returns 54.59
|
fact
|
fact(double_expr)
|
Returns the factorial of a number.
|
fact (20) returns 922337203
|
floor
|
floor(double_expr )
|
Returns the largest integer not greater than x , as a double number.
|
floor (9.87) returns 9
|
log
|
log(double_expr )
|
Returns the natural logarithm of the number you specify as double_expr. You cannot use negative numbers.
|
log(40) returns 3.68
|
PI
|
PI
|
Returns the machine-dependeant value, for example: 3.141592653589793.
|
|
pow
|
pow(numeric_expr1, numeric_expr2)
|
Raises the value of numeric_expr1 to the power of numeric_expr2.
|
pow (2, 8) returns 256
|
rand
|
rand()
|
Returns a random number greater than or equal to zero and less than one. The returned value changes each time you recalculate. This function takes no arguments.
|
rand () can return .783
|
round
|
round(numeric_expr )
|
Rounds the numeric expression to the nearest integer.
|
round (1234.56789) returns 1234.568
|
sign
|
sign(numeric_expr )
|
Returns the sign for the numeric_expression. If the number you specify is positive sign returns 1. If the number is negative, sign returns -1. If you specify 0, sign returns 0.
|
sign (7) returns 1
|
sin
|
sin(double_expr )
|
Returns the sine of double_expr , in radians.
|
sin (1) returns -1.57
|
sqrt
|
sqrt(double_expr )
|
Returns the positive square root of double_expr .
|
sqrt (16) returns 4. Returns an error if double_expr is negative.
|
tan
|
tan(double_expr )
|
Returns the tangent of an angle expressed in radians.
|
tan (1.57) returns 0
|
uudigen
|
uuidgen()
|
Returns a unique user ID.
|
|
String functions 
String functions allow you to manipulate strings in various ways. The following table describes the functions supported in SilverStream.
Function
|
Syntax
|
Description
|
Example
|
concat
|
concat (string_expr1, string_expr2)
|
Returns the result of appending the second string value to the first.
|
concat('XY','AB')
returns XYAB.
|
indexOf
|
indexOf (string_expr1, string_expr2 [int offset])
|
Returns the position of string_expr2 in string_expr1. [int offset] indicates the number of characters to skip at the beginning.
If the second string is not found, -1 is returned. The search starts from the left and goes to the right. Both the result and the offset are zero (0) based.
|
Examples using the indexOf() function
|
inlist
|
inlist(string)
|
Returns table rows based on the expression being used.
You can only use this function within an in() or userin() list.
|
Example of user() and inlist() functions
|
lastIndexOf
|
lastIndexOf (string_expr1,string_expr2 [int offset])
|
Returns the position of string_expr 2in string_expr1. [int offset] indicates the number of characters to skip at the beginning. If the second string is not found, -1 is returned. The search starts from the right and goes to the left. Both the result and the offset are zero-based.
|
Example using the lastIndexOf() function
|
left
|
left(string_expr, integer_expr)
|
Returns the leftmost characters in a string.
|
left('nancy', 3)
returns 'nan'.
|
length
|
length(string_expr)
|
returns the length of the string.
|
length('nancy')
returns 5.
Example using the length() function
|
lower
|
lower(string_expr)
|
Returns a string expression with all the letters in lower case.
|
lower("ABCD")
returns abcd.
|
ltrim
|
ltrim(string_expr)
|
Returns a string without any leading spaces (on the left side).
|
ltrim (" hello")
returns "hello"
|
replace
|
replace (string_expr1, string_expr2, string_expr3)
|
Returns the string1 with any occurrences of string_2 replaced with string_3.
|
replace
("SilverStream', 'Silver', 'Gold')
returns GoldStream.
|
right
|
right(string_expr, integer_expr)
|
Returns the rightmost characters in a string.
|
right('Nancy', 3)
returns 'ncy'.
|
substring
|
substring(string_expr,begin_number, [end_number])
|
Returns a specified number of characters from a begin_number position in the input string. If the end_number is omitted, all characters from the specified begin_number to the end of the string are returned. The result is zero-based.
|
Example using the substring() function
|
trim
|
trim(string_expr)
|
Returns a string without any leading or trailing spaces (on the right side).
|
trim(" dog " )
returns ("dog").
|
upper
|
upper(string_expr)
|
Returns a string expression with all letters in upper case.
|
upper("abcd")
returns ABCD.
|
user
|
user()
|
Returns the userid of the current user. If not logged on, returns "anonymous"
|
Example of user() and inlist() functions
|
Examples using the indexOf() function
This function returns the position of a substring within another string, reading the string from right to left. The [int offset] parameter indicates the number of characters to skip at the beginning. For example:
Example
|
Result
|
indexOf ('abcdefg','cde')
|
3
|
indexOf('ababab','a')
|
0
|
indexOf `ababab', `a', 1)
|
2
|
Example using the lastIndexOf() function
This function is similar to the indexOf() function except that it reads the string from right to left. the following table shows some examples.
Example
|
Result
|
lastIndexOf('abcdefg','a')
|
0
|
lastIndexOf('ababab','a')
|
4
|
lastIndexOf('ababab','a', 1)
|
2
|
Example using the length() function
This example uses the length() function in a validation expression. It is written for a text field and verifies that the text field must be 4 character long:
length(companies.companyshortname )= 4
Example using the substring() function
This function returns a specified number of characters from a begin_number position in the input string. If the end_number is omitted, all characters from the specified begin_number to the end of the string are returned.
Example
|
Result
|
substring (`abcdefghij', 5)
|
fghij
|
substring('abcdefghij',2,3)
|
cde
|
Example of user() and inlist() functions
The user() function gets the user login name of the current user. The inlist() function returns the table rows that match a string in a table. The inlist() function must be used within either an in() string operator or a userin() operator. For example:
user() userin(inlist(Project.Allowed_groups))
returns all table rows form the Project table that contain a string that matches the login name.
NOTE For security expressions use the userid() security function, which returns the UUID rather than the login name. For more information, see
Security functions.
Parse functions 
Parse includes a set of functions that convert a string formatted according to date or number formatting rules into an object. List of parse functions. The parse() function has several variants that allow you to specify a string expression, input pattern, time and date style, locale, and timezone.
For information about using the parse() variants, see AgParse in the SilverStream on-line API documentation.
The following is a list of the parse functions that the SilverStream Expression Builder supports. The input string can be from an object such as a text field or a database field.
Function
|
Description
|
parseDate
|
Takes a date string and returns a java.sql.Date object.
|
parseDouble
|
Converts a string to an Object of type double.
|
parseLong
|
Converts a string to an Object of type long.
|
parseTime
|
Takes a time string and returns a java.sql.Time object.
|
parseTimeStamp
|
Takes a timestamp string and returns a java.sql.Timestamp object.
|
Examples of parse expressions
The expression below converts a date string in the format of Wednesday, June 2, 1999 to a java.sql.Date object:
parseDate(DateString, "EEE, MMM d, yyyy")
The expression below converts a string in the format of Thu 11:59:59 PM to a java.sql.Date object.
parseTime(TimeString, "E h:mm:ss a")
This expressions converts a string in the format of June 2, 1999 5:30:59 PM EST to a java.sql.Timestamp object.
parseTimeStamp(TimeStampString,"MMMM dd, yyyy h:mm:ss a zz")
In the following expression, "numeric" is a data-bound text field with the value 1234.1234: (Two places are returned because the default format is specified):
parseDouble (numeric, " ")
returns 1234.12
This example uses the same input with parseLong():
parseLong (numeric, " ")
returns 1234
The next example specifies an input pattern with parseLong():
parseLong ("32767", "#####"), "#####%")
returns 3276700%
Note that like all parse functions, parseLong() returns an Object. If you want to return a formatted string you could use the format() function:
format (parseLong ("1234.1234", "#####.####"), "$#####.00")
returns $1234.00
For information about patterns and styles, see
Formatting Patterns and Styles.
Format() function 
The format() function translates an object into a formatted output string (the opposite of parse). The format() function applies to all data types. The format() function has several variants that allow you to specify the object, object pattern, time and date styles, locale, and timezone formats.
For information about using the format() variants, see AgFormat in the SilverStream on-line API documentation.
Examples of format expressions
The following examples show parse expressions (shown in the previous section on Parse) used as arguments for a format expression. It converts a date object in the format of Wednesday, June 2, 1999 to the format 06/02/99
format(parseDate(someDateString, "EEE, MMM d, yyyy" ),"MM/dd/yy" )
This expression returns the default format of the Date object (or any other object).
format(parseDate( someDateString, "EEE, MMM d, yyyy" ), "" )
This example changes the format of the time to 11:59 PM using these parameters:
format( parseTime( someTimeString, "E h:mm:ss a" ), "h:mm a" )
This changes the format to 1999-06-02 18:30:59:
format( parseTimeStamp( someTimestampString, "MMMM dd, yyyy h:mm:ss a zz"), "yyyy-MM-dd HH:mm:ss" )
Here are examples of formatting numeric. For the value 0.5:
format( numericValue, "##.00%") returns 50.00%
format( numericValue, "$####.##") returns $.5
For information about patterns and styles, see
Formatting Patterns and Styles.
Aggregate functions 
Aggregate functions operate on multiple results from a table: the whole table, or a result set. Note that Aggregate functions are calculated on the client, not the server. The following table lists the functions supported by SilverStream..
Function
|
Syntax
|
Description
|
Example
|
sum
|
sum(argument [FOR ALL])
|
Returns the sum of the values in the argument. NULL values are not included. sum is valid for all numeric datatypes.
|
In the following example, the table column quantity contains the values 2 and 4.
sum (quantity)
returns 6
|
avg
|
avg(argument [FOR ALL])
|
Returns the average value of argument as a double. A double is a floating point Java-based data type. NULL values are not included. avg is valid for all numeric datatypes.
|
In this example, the table column quantity contains the values 30 and 40.
avg (quantity) evaluates to 35.
|
count
|
count(arg) [FOR ALL])
|
Returns the number of rows in a table column that satisfy the query as a long. Rows with NULL in the specified column are not included.
|
In the following example, a table contains a column called miscellaneous that contains the following values:
117, apple, 13, 14, pear, kumquat, eskimo, 40
count(miscellaneous) returns 4
where 4 is the number of rows containing numeric values.
|
count*
|
count(*[FOR ALL])
|
Returns the number of rows that satisifies the query, including nulls, as a long.
|
In the following example, all the columns in a table are included in the query, regardless of the type of data they contain.
count (* for all)
returns
27
where 27 is the total number of rows contained in the table.
|
max
|
max(column [FOR ALL])
|
Returns the maximum value in the specified column or set of columns. NULL values are not included. max is valid for long and numeric fields, strings, and date/time/timestamp columns. With character columns, MAX finds the last value in the sort sequence.
|
max(salary)
returns 65000.00
where 65000.00 is the largest value in the specified column.
|
min
|
min (column [FOR ALL])
|
Returns the minimum value in the specified column. NULL values are not included. MIN is valid for numeric fields, strings, and date/time columns. With character columns, MIN finds the first value in the sort sequence.
|
min(salary) returns 1000.00
where 1000.00 is the smallest value in the column.
|
small
|
small(return_expr column,
low_ranking [FOR range|ALL])
|
Returns the smallest value (low_ranking) in a dataset and also returns another column or expression value based on the result.
|
For example, a low_ranking of 4 finds the fourth smallest value.
small(employeeName, Salary, 4)
returns the name of the employee with the fourth smallest salary.
|
large
|
large(return_expr,
column, high_ranking [FOR ALL])
|
Returns a column value for the row with a high rank in another column.
|
A high_ranking of 3 finds the third largest value.
large (employeeName, Salary, 3)
returns the name of the employee with the third highest salary.
|
Examples of using aggregate functions
For example, if you wanted to add a column that contains the total price for an order, you would create an expression using the sum function that combines the values of two other columns in your table. The expression would look this:
sum(orderdetails.unitprice * orderdetails.quantity)
Assume a column of values:
-1234, -56, -1, -32767, 0, 32767, 1, 56, 1234, NULL.
The following table shows the results of using aggregate functions:
Example
|
Result
|
sum( tableName.columnName)
|
0
|
sum( tableName.columnName)
|
9
|
sum( tableName.columnName)
|
10
|
avg( tableName.columnName)
|
0
|
avg( tableName.columnName)
|
-32767
|
FOR ALL option
Use the FOR ALL option with any aggregate function to include all the bands and sub bands in a view. (FOR ALL is assumed when used in forms). For example:
count(tablename.bandname FOR ALL)
returns the band count for the current band and all other sub-bands in the view.
Date and time functions 
Date and time functions allow you to write expressions that return current times, dates, days, and other time values. If you use a date before 1970, you must enter it in ANSI SQL format for it to be parsed. The format is: yyyy-mmm-dd hh:mm:ss. Enclose date/time/timestamp literals in single or double quotes. The following table summarizes the date/time functions available in the SilverStream Expression Builder.
Function
|
Syntax
|
Description
|
Example
|
day
|
day(date_expr)
|
Returns the day of the week as a number in a range from 0-6.
|
day(today()) where today is Wednesday, returns 3.
|
day Number
|
dayNumber (date_expr)
|
Returns a number for the day within the month. The valid range is 1-31.
|
dayNumber(orderdetails.date)
where the date contained in the column is May 8, 1997. It returns 9.
|
hour
|
hour(time_expr)
|
Returns the hour of the day as an integer value.
|
hour ("13:42:00") returns 13.
|
minute
|
minute(time_expr)
|
Returns the minute of the hour as an integer value. The range is 1-12.
|
minute ("10:24:00"") returns 24.
|
month
|
month (date_expr)
|
Returns the month as an integer value. The range is 1-12.
|
month("1997-8-28") returns 8.
|
nanos
|
nanos (timestamp_expr)
|
Returns the time in millions of a second. You must include a timestamp field in the argument.
|
|
now
|
now()
|
Returns the current date and time as a timestamp value. You do not need to include an argument with this function.
|
Returns a value like 10/15/99 11:35
|
second
|
second(time_expr)
|
Returns the number of seconds in the time_expr. The valid range is 1-59.
|
second("11:26:52")
returns 52.
|
timeOfDay
|
timeofday()
|
Returns the current time as a time value. You do not have to specify an argument for this function.
|
timeofday() can return 23:32 if the current time is 11:12.
|
today
|
today()
|
Returns the current date as a date value. You do not need to include an argument with this function.
|
today() returns a value like 10/15/97.
|
year
|
year(date_expr)
|
Returns a number that corresponds to the year in the date expression.
|
year(now()) where now() corresponds to the year 1997, returns
1997
year("2005-4-6")
returns 2005
NOTE In previous versions of SilverStream, this function returned two characters to the year in the date expression minus 1900.
|
Example of using day(), now(), and hour() functions
For example, you can write security expressions that restrict object access to certain days or times of day. The following example shows a usage of the day, now, and hour functions:
(1<=day(now())) and
(5>=day(now())) and
(9<=hour(now())) and
(17>=hour(now()))
This expression specifies Monday through Friday inclusive, between the hours of 9 a.m. and 5 p.m.
Security functions 
Security functions (labeled `IDs" in the Expression Builder) work with Universally Unique Identifiers (UUID standard). These functions are used for restricting access to selected objects.
For more information about writing security expressions, see the chapter on security in the Administrator's Guide.
Function
|
Syntax
|
Description
|
userID
|
userid()
userid('name')
|
userid() returns the UUID of the user that is currently logged on.
NOTE: You cannot include userid() and groupid() in the same expression.
|
UUID
|
UUID('uuidstring')
|
Returns the UUID that corresponds to the string representation of the UUID (the userID).
Use this form when you are unable to resolve a group or user ID.
|
groupID
|
groupID('name')
|
Returns the UUID of the group you specify. Like user names, group names must be qualified.
You can only use constant strings as an argument.
NOTE: You cannot include userid() and groupid() in the same expression.
|
Operator and function support for data types 
This section lists the operators and built-in functions that the Expression builder tool supports for each data type.
Integer data types 
The Expression Builder supports these operators for integer data types:
-
Arithmetic operators: - negate
-
Relational operators: >, < , = , >=, <=, =, <>, not =, in, not in, between, not between, isnull, not isnull
-
Functions: abs, sign, format
Long data types 
The Expression Builder supports these operators for long data types:
-
Arithmetic operators: +, -, *, negate
-
Relational operators: >, <, =, >=, <=, =, <>, not =, in (set of values separated by commas), not in, between, not between, isnull, not isnull
-
Functions: abs, sign, format
-
Aggregate Functions: count, count*, large, max, min, small, sum
Decimal data types 
The Expression Builder supports these operators for numeric data types:
-
Arithmetic operators: +, -, *, negate
-
Relational operators: >, <, =, >=, <=, =, <>, not =, in, not in, between, not between, isnull, not isnull
-
Functions: abs, sign
-
Aggregate Functions: avg, count, count*, large, max, min, small, sum
Double data types 
The Expression Builder supports these operators for double data types:
-
Arithmetic operators: +, -, /, *, negate, ^ (exponent), % (remainder)
-
Relational operators: >, <, =, >=, <=, =, <>, not =, in ( set of values separated by commas), not in, between, not between, isnull, not isnull
-
Functions: abs, acos, asin, atan, atan2, ceil, cos, exp, fact, floor, log, pi, format, pow, rand, round, sign, sin, sqrt, tan
-
Aggregate functions: avg, count, count*, max, min, large, small, sum
Boolean data types
The Expression Builder supports these operators for boolean data types:
-
Relational operators: = <>, not =, in ( set of values separated by commas), not in, isnull, not isnull
-
Logical connectors: and, or, not
Date data types 
The Expression Builder supports these operators for date data types:
-
Relational operators: >, < , = , >=, <=, =, <>, not =, in, not in, between, not between, isnull, not isnull
-
Functions: day, day number, format, month, today, year
-
Aggregate functions: count, count*, max, min, small, large
-
Enclose date/time/timestamp literals in single or double quotes as shown in the following example.
"1996-6-30"
Parsing of date constants is context-sensitive: they are interpreted as strings first and converted into date objects only when there is a date, time or timestamp variable in the expression.
String data types 
The Expression Builder supports these operators for string data types:
-
Arithmetic operators: + (concatenates strings with other strings and also double, numeric, long, integer and boolean variables and constants making all necessary conversions).
-
Relational operators: >,<,=,>=, <=, =, <>,not =, in ( set of values separated by commas), not in, between, not between, like, not like, userin, not userin, isnull, not isnull, startsWith, not startsWith, endsWith, not endsWith, equalsIgnoreCase, not equalsIgnoreCase
-
Functions: concat, indexof, lastindexof, left, length, lower, ltrim, parselong, parsedouble, parsedate, parsetime, parsetimestamp, replace, right, substring, trim, upper, user
-
Aggregate Functions: count, count*, large, max, min, small
Time data types 
The Expression Builder supports these operators for time data types:
-
Relational operators: >, < , = , >=, <=, =, <>, not =, in, not in, between , not between, isnull, not isnull
-
Functions: format, hour, minute, second, timeofday
-
Aggregate functions: count, count*, large, max, min, small
Timestamp data types 
The Expression Builder supports these operators for timestamp data types:
-
Relational operators: >, < , = , >=, <=, =, <>, not =, in, not in, between , not between, isnull, not isnull
-
Functions: day, day number, format, hour, minute, month, nanos, now, second, year
-
Aggregate functions: count, count*, large, max, min, small
UUID data types 
The Expression Builder supports these operators for UUID data types:
-
Relational operators: = , <>, not =, not isnull, isnull
-
Functions: uuidgen, userid(), userid(string), groupid(string), UUID(string)
BLOB data types 
The Expression Builder supports two types of BLOBs: Java and database. There is no length restriction for this data type; it depends on the back-end database being used. Use this data type for input/output only. When you define a table in the Table Designer, you should define a BLOB as null.
Copyright © 2000, SilverStream Software, Inc. All rights
reserved.