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

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

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 )

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.

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

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

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.

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

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

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.

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

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.

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.

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.