General Reference
Chapter 1 Expressions, Operators, and Builtin 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.
Floatingpoint literals
Floatingpoint literals have an integer part and a decimal part, for example 3.141592. They result in a floatingpoint 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 floatingpoint 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 10e5.
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:
`19980612'
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

Builtin 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 arccosine (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 arcsine (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 arctangent (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 machinedependeant 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 zerobased.

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 zerobased.

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 online 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 databound 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 online 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 19990602 18:30:59:
format( parseTimeStamp( someTimestampString, "MMMM dd, yyyy h:mm:ss a zz"), "yyyyMMdd 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 Javabased 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 rangeALL])

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 subbands 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: yyyymmmdd 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 06.

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 131.

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 112.

minute ("10:24:00"") returns 24.

month

month (date_expr)

Returns the month as an integer value. The range is 112.

month("1997828") 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 159.

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("200546")
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 builtin 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.
"1996630"
Parsing of date constants is contextsensitive: 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 backend 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.