Returns the average value of a set of values, excluding $Null values.
Syntax
$average(expression)
Parameters
Return Value
Number, with the number of decimal places implied by expression.
Comments
The function calculates the average of a set of values, excluding $Null values.
Expression is often a WHERE expression that includes only selected values in the averaging operation. If the WHERE expression (expr1 WHERE expr2) is true, the expr1 value is included in the averaging operation; otherwise, expression is considered $Null and is not included in the averaging operation.
Example
compute Employees where DeptName="Sales"
evaluate (let AvgAge = $average($year($date)-$year(BirthDate)))
(let AvgSal = $average(Salary where Sex = "F"))
Finds the average age of all employees, and the average salary of all female employees, in the sales department.
report footing
$average(Salary)
($total(Salary*Salary) / $count(Salary) - $average(Salary) * $average(Salary))
Reports average salary and salary variance.
See Also
$count
$max
$min
$total
How to Use The Report Generator
WHERE
Builds a single character string out of separate strings, inserting a separator string.
Syntax
$squeeze(separator «,string»)
Parameters
| separator | A character string, or an expression that evaluates to a character string. Separator is placed between each instance of string. |
| string | A character string, or an expression that evaluates to a character string. Leading and trailing blanks are trimmed from string. Each string must be separated from the next by a comma. |
Return Value
Character string.
Comments
$squeeze combines specified strings into a single string, with separator placed between each string. In the function call, each string must be separated from the next by a comma. Leading and trailing blanks are trimmed from each string.
Example
$squeeze (",",LastName,FirstName,Salutation)Evaluates to “Smith,John,Mr.” when FirstName is “John “, LastName is “Smith “, and Salutation is “Mr. “.
list all Ents format $squeeze ("",EntName,EntType,AvgSize,DirName)Compresses the LIST output as much as possible.
Related Information
$concat
UNION
Combines the members of two or more result sets into a single set.
Syntax
set1 UNION set2
Parameters
| set1 | The name of a result set that resulted from the execution of a set-producing command.
Set1 and set2 must have the same component structure. |
| set2 | The name of a result set that resulted from the execution of a set-producing command.
Set1 and set2 must have the same component structure. |
Comments
When used within a FIND command, the set that results from the application of UNION contains all of the records from the specified sets, with duplicates being eliminated.
The sets must have the same component structure.
Example
find Employees (unrelated) WorkFor Managers keep Employees
find current (Employees -> Managers) -> MgrSet
% top level managers
find Employees WorkFor MgrSet keep Employees -> EmpSet
while
find Employees WorkFor EmpSet (Employees -> Managers) -> EmpMgrSet
if $setcount = 0
break
endif
find EmpMgrSet keep Managers -> NewMgrSet
find MgrSet union NewMgrSet -> MgrSet
endwhile
While the preceding program demonstrates the use of UNION, the program can be replaced with the single command shown below.
find Employees WorkFor Managers keep Managers -> MgrSet
See Also
INTERSECT
MINUS
Calculates a date value by adding weeks to (or subtracting weeks from) a specified date value.
Syntax
$addweeks(date,number)
Parameters
| date | a date, or an expression that evaluates to a DATE data type |
| number | a number, or an expression that evaluates to a number |
Return Value
Number, representing a DATE value.
Comments
Use $addweeks to perform arithmetic with date values. The $addweeks function calculates a date value by adding a number representing weeks to a date value. If number is negative, the effect is to subtract the weeks from the date.
The + (add) and – (subtract) operators can be used to achieve the same results.
If date or the result of the $addweeks expression is an invalid date (e.g., 19930231), it is adjusted to produce a valid date (e.g., 19930228).
Example
If $Date has the value 19981225, then
$addweeks($date,1)
Evaluates to 19990101.
$addweeks($date,-2)
Evaluates to 1998.501.
$addweeks(19980201,4)
Evaluates to 19980301.
See Also
$adddays
$addmonths
$addyears
$todate
$weekday
+ (Add/Positive)
– (Subtract/Negative)
About Functional Expressions
Arithmetic with Dates
Generates the “sounds-like” value of a string.
Syntax
$soundex(source)
Parameters
| source | a character string, or an expression that evaluates to a character string |
Return Value
Character string (alpha).
Comments
$soundex yields a “sounds-like” value for source. The value is created using an heuristic algorithm and is based on English pronunciation.
When $soundex is applied to two separate strings and they generate the same “sounds-like” value, then the two original strings can be assumed to sound the same. $soundex can be used to compare and find strings that sound the same but are spelled differently.
The result of $soundex is of data type ALPHA, therefore comparisons involving soundex values are case-insensitive.
Example
find all Customers where $soundex(LastName) = $soundex("Burton")The above command returns customer records for names like Burton, Berton, Bertyn, Bertin, BURTON, BERTIN, and so on.
find all Customers where sLastName = $soundex("Burton")The preceding command takes advantage of indexed searching by setting up sLastName as an indexed virtual field that contains the soundex values of LastName in the Customers EntitySet.
let SoundsLike = $soundex("Smith")
find all Customers where sLastName = SoundsLike?The preceding code fragment uses prefix searching (? wildcard) with soundex values.
See Also
? (Wildcard)
About Character Literals
About Functional Expressions
Outputs the results of one or more expressions.
Syntax
OUTPUT [expression] [;]
Parameters
| expression | Any valid expression. Complex expressions must be enclosed in parentheses. When you specify more than one expression, each must be separated from the next by at least one space. |
Comments
OUTPUT evaluates expression and sends the result to the current output device. If expression uses fields from the current set, the values are taken from the current member in that set.
OUTPUT sends one line of characters to the current output destination (application document). The software automatically terminates the output line with a carriage return/line feed combination. You can suppress the carriage return/line feed combination by placing a semicolon (;) at the end of the OUTPUT command.
You can redirect output from the terminal using a SET OUTPUT command.
To specify the number of spaces to appear between output values, use a SET COLUMNSPACING command.
Example
To send the values of the variable TestVar and the form field fEmpform.
output TestVar fEmpform.LastName
To send the values from two OUTPUT commands to one line of the application documents MyDoc, enter
set output MyDoc
output TestVar;
output fEmpform.LastName
The following commands cause the character string following OUTPUT to display on the current output device:
output “Please enter your first and last names: “;
input FirstName LastName
Important
If it is needed the issue the INPUT command in the same line as its corresponding text, the colon “:” must be present at the end of the text, just like the example above.
See Also
INPUT
SET COMPILEMODE
Controls the behavior of the Compiler.
Syntax
SET COMPILEMODE SQLMODE | ZIMMODE | SERVERMODE
Comments
The COMPILEMODE option is set to SQLMODE by default. The compiler generates SQL database code—that is, code bound to a particular SQL database type.
In ZIMMODE, EntType, RelType, and so on, are ignored. The compiler generates standard Zim code.
In SERVERMODE the compiler generates Zim Server code when the objects are defined as “zimserv” (or an alias of it). SERVERMODE is the preferred setting since ZIM execution is most often more efficient. Also, Zim Server requests if all entity sets and relationships in the command are for the same Zim server.
When COMPILEMODE is SQLMODE, Zim generates code that is bound to a particular SQL database type. By setting COMPILEMODE to ZIMMODE, Zim generates standard Zim code.
If a program has been compiled with SERVERMODE or SQLMODE on, the program is flagged as compiled for a server and can only be executed in SERVERMODE or SQLMODE. In ZIMMODE, these types are ignored and all entity sets and relationships are assumed to be part of the local Zim database. During compilation in ZIMMODE, no server requests are stored in the compiled code and the program is flagged as compiled for Zim. Executing such a program never results in server access.
Example
To compile a Zim Client-server application to run as a standard Zim application, enter
> set compilemode zimmode
See Also
COMPILE
SET EXECUTEMODE
SET SQLCOMPILE
UPDATE
Compares one value to a list of values.
Syntax
expression [NOT] IN («expr»)
Parameters
| expression | An expression that evaluates to either a number or a character string. |
| expr | A list of expressions whose values are to be compared to expression. Members of the list are separated from one another by commas. |
Return Value
Logical
Comments
An IN comparison is logically true if expression is equal to at least one of the values in the specified list.
A NOT IN comparison is logically true if expression is not equal to any of the values in the specified list.
In either case, the comparison ends as soon as a logically true instance is found.
Example
The conditional expression that follows is logically true if the value of AirPollutants matches any of CFC, CO, or NOX.
AirPollutants in (" CFC","CO"," NOX")The conditional expression that follows is logically true if the value obtained from either TicketAmt*5 or Receipts*10 equals 10,000.
10000 in ( TicketAmt * 5,Receipts * 10)
See Also
About Conditional Expressions
Copies files from the client side to the server side.
Syntax
$copytoserver(source file name, destination file name, file type)
where
| source file name | an expression that evaluates to a character string containing a valid file address |
| destination file name | an expression that evaluates to a character string containing a valid file address |
| file type | an expression that evaluates to a character string that starts either with “B” (from “Binary”) or “T” (from “Text”) |
Return Value
The value return by this function is the file address of the destination if it is executed correctly; otherwise, it will return a $Null value.
Comments
This function is designed to work under ZIMTC and copies file from the client side to the server side using the file type specified. Files of the binary type are sent as they are, without their contents being checked (like images, videos, etc). On the other hand, files of the text type are copied using text file conventions for Windows and Unix.
Both the source and the destination file names can be either relative or absolute file paths. The following remarks apply to these paths:
| source file is absolute | the file is taken from the absolute path. Care should be taken because this absolute path refers to a location in the client’s machine; if there are many different users running, all users must have the same valid address |
| destination file is absolute | the file is written onto the absolute path |
| source file is relative | the file is read from a place that is relative to the current directory in the client session which is always where the Zimtc session has been started |
| destination file is relative | normally, the current directory is the database directory; therefore, the file will be written on a place inside the current directory |
If the destination file name contains sub-directories that don’t exist, they are created.
The ZIM executable ignores this function as it makes no sense copying a file to itself.
Example
This example copies the text file from the database path to a fixed location in the server’s machine.
out $copytoserver("mytext.txt", "c:\mextext.txt", "T")Related Topics
$copytoclient
Specifies the current field delimiter.
Syntax
SET DELIMITER character
Parameters
| character | Any valid character. The space, forward slash, comma, and quotation mark are the most commonly used.
If character is not a letter or a digit, it must be enclosed in quotation marks.
If character is a special character such as space or backslash, it must be enclosed in quotation marks.
If character is a string longer than one character in length, only the first character in the string is used as the delimiter. |
Comments
The default field DELIMITER is the space character.
The specified character is used as a delimiter by the INPUT command (input from the terminal), by the ADD and CHANGE commands (input from an unstructured application document), and by the LIST and OUTPUT commands (output to the terminal or an unstructured application document) in either normal or comma-delimited format.
If the space is specified as the delimiter, then tab is also acknowledged as a delimiter, and multiple spaces are considered to constitute a single delimiter.
In comma-delimited format, the space causes the comma to be used as the delimiter for both input and output.
Example
set delimiter “/”
set delimiter ‘”‘
set delimiter “‘”
In the preceding examples, the slash, double quotation mark, and single quotation mark, respectively, are specified as the delimiter. (Only one delimiter character can take effect at one time.)
See Also
SET INPUT FORMAT
SET OUTPUT FORMAT
SET SPECIALSCAN
SET TEXTDELIMITER
Portuguese 
English