Jive Expressions & Functions

Expressions

Expressions are used to set variables and in if and while control commands. They are also used to output data for HTML. A standalone expression is converted as needed to string and sent to the HTML output page.

Expressions consist of value references and operators. Value references are variable names or literals. Basic SQL operators are used. Two special operators are supported - at sign (@) and pound sign (#). A set of functions are available.

The precedence of operators is:

#
( )
+ - (unary)
* /
+ - (binary)
= < > <> <= >= between like in
not
and
or
@ operator

Operations on nulls are the same as SQL. Three valued logic is used.

The special operators (@, #) are used with arrays and are described under var Command.

The SQL-style operators between, like and in are described under between, like, in.

Functions

Functions are used in expressions. They take one or more expressions as arguments and return a single value. Function arguments are normally enclosed in parentheses. Multiple arguments are separated by commas.

Jive functions are:

format Function, formats a value using a picture.
isunk Function - tests an expression for null or unknown.
ifunk Function - allows a substitute value for a null expression.
lower, upper Functions - convert a string to all lower-case or upper-case.
replace Function - replaces each instance of a character in a string with a sub-string.
substr Function - extracts a sub-string.
Date/Time Functions - extract sub-field, add interval to date, compute interval differences for dates.

Preset Variables

At startup, Jive defines a set of variables at the root level. They are normal variables. Their values may be changed or their definitions overridden. Jive preset variables:

Name	Type	Description
cr	string	control character - new line
true	bool	boolean true
false	bool	boolean false
calender	date	calender date at startup
clock	time	clock time at startup
now	timestamp	timestamp at startup

Intrinsic Values

Intrinsic values are used to determine results from SQL, input and env commands. Two values are provided - error and empty, plus a special variable - rows.

The error value is set following a SQL, input or env command. It indicates if an error has occurred in processing the command. error is true following an input or env command if a type other than string was defined and there was a conversion failure, otherwise it is false. error is true following a SQL command if the SQL command caused an ODBC error, otherwise it is false.

empty is set following a SQL command. It indicates that the SQL command processed no rows. Some ODBC drivers cannot detect empty conditions on update, delete and/or insert. In this case, empty is null.

error and empty are available for a query (select) following the {end} terminating the select.

rows is a special variable like the preset's above, but its value changes after each SQL command. After update, delete and/or insert, it will contain the row count if available, otherwise it will contain -1.