Assigns a name to the set of records processed by the main command.
Syntax
-> setname
The set of records processed by the main command is given the name setname.
where
| setname | The name to be assigned to the result set. |
Comments
You can explicitly create a result set by adding the characters -> and an appropriate name to the end of any set processing command.
The result setname can be:
- the name of a set object in the database. The set specification used in the main command must be consistent with the structure defined for the set object in the Object Dictionary.
- the name of a result set previously created by a set-producing command such as FIND or DELETE. The set specification used in the main command must be consistent with the structure of the previously named result set unless a SET CHECKSETS OFF command has been issued.
- a new name
Example
find Employees where LastName = "Jones" -> JSet
The result set JSet contains all records from Employees in which the last name is Jones.
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
COMPLETE
Used as a part of a set specification to select both matching and non-matching members of an EntitySet, a relationship with fields, a form, a menu, a result set, or a structured application document.
Syntax
object (COMPLETE)
Parameters
| object | The name of an EntitySet, a relationship with fields, a structured application document, a form, a menu, or a result set. Where applicable, may be a role name. |
Comments
COMPLETE, when used in a set specification, selects all records in the associated object regardless of whether they satisfy a modifying relationship. This function is related to the theoretical partial outer join operation.
Note: When using the WHERE clause with COMPLETE, if the conditions specified by the WHERE clause are not met, then no data is returned, even though the COMPLETE option has been specified.
Example
list Employees (complete) WorkIn Departments
Lists all employees regardless of whether they work in a particular department. If some employees lack corresponding Departments values, then the Departments fields in the list of composite records are $Null.
find all Managers (complete) Manage Employees
Lists all managers regardless of whether they currently have a staff.
See Also
ADD
CHANGE
COMPUTE
DELETE
FIND
LIST
REPORT FROM
SELECT
UNRELATED
UPDATE
USING
SORTED BY
Sorts the members of the set produced by the main command.
Syntax
SORTED BY «expression [ASCENDING|DESCENDING]»
Parameters
| expression | An expression that identifies a field to be used as a sort key. Complex expressions must be enclosed in parentheses. |
| ASCENDING or DESCENDING | Specifies how the sort on the associated key is to be performed.
ASCENDING (default)
Sorts in “alphabetical order” (A-Z, 0-9).
DESCENDING
Sorts in “reverse alphabetical order” (Z-A, 9-0). |
Comments
The sorting clause determines the order in which the selected records are to appear in the set. The fields to be used as sort keys (identified by each expression) must be part of the set produced by the main command.
If you omit the sorting clause, the software determines the order of the records.
Note: Existing result sets can also be sorted after selection – with the SORT command.
Example
list all Employees sorted by Department descending FirstName LastName
The preceding command lists all Employees by department (in descending order) and, within department, by first and last names (in ascending order).
report from Invoices
sorted by {$month(InvoiceDate)-5
where $month(InvoiceDate) >= 6,
$month(InvoiceDate)+7}
:
endreport
The preceding program fragment reports invoices in order by fiscal month, assuming that the fiscal year begins in June.
See Also
SORT
UNRELATED
In a set specification, selects non-matching members of an EntitySet, a relationship with fields, an application document, or a result set.
Syntax
object (UNRELATED)
Parameters
| object | The name of an EntitySet, a relationship with fields, a form, a structured application document, or a set. Can be a role name for an EntitySet or relationship with fields. |
Comments
In a set specification, UNRELATED selects all records in object that do not satisfy the relationship condition expressed in the set specification.
UNRELATED can be used only once in a set specification.
This function is related to the theoretical partial outer join operation.
Example
> find all Employees (unrelated) WorkIn Departments
> list all
LastName FirstName DeptNum DeptName DeptNum
Johnston Leslie D07
Nelson John
Kelly Pat
The preceding command finds all employee records that contain no DeptNum, or contain a DeptNum for which there is no corresponding department record. Missing values are undefined ($Null).
find all Employees WorkIn Departments (unrelated) keep Departments
The preceding command creates a list of all departments that have no employee records associated with them.
find all Managers (unrelated) Manage Employees
Finds all employees that do not manage anyone. (For a contrasting example, see COMPLETE.)
See Also
– (Dynamic Rename)
COMPLETE
USING
Indicates the reason that a deadlock condition occurred.
Syntax
$deadlockreason
Return Value
A character string. Cannot be reset by an application program.
Description
$DeadlockReason contains one of the following reason codes for the current deadlock condition:
| Code | Reason |
| 0 | transaction completed normally |
| 1 | transaction terminated due to time out |
| 2 | transaction terminated due to transaction recovery |
| 3 | not in use |
| 4 | attempt to do an update in a read transaction |
| 5 | transaction terminated as a result of deadlock |
| 6 | lock request outside a transaction (warning only) |
| 7 | problem converting operating system lock (transaction terminated) |
| 8 | no free file table entry (transaction terminated) |
See Also
SET DOCUMENT FORMAT
System Variables
A “null” value for comparison.
Syntax
$null
Value
“Null” (a state of valuelessness). Cannot be reset by an application program.
Description
The “null” property. $Null can be written without the dollar sign.
When certain atomic expressions (variable name, field name, form field name, or formal parameter name) are used before being assigned values, or if the values are unknown, those expressions are considered to be $Null.
$Null is a property indicating a state of valuelessness; $Null is not itself a value. The null string is a value and is therefore not the same as the $Null property.
An arithmetic or functional expression that includes a $Null argument or operand always evaluates to $Null.
A logic expression that includes a $Null operand always evaluates to $False.
Because $Null is not a value, you cannot use the conditional operators = (Equals) and <> (Not Equals) to determine if an expression is $Null. Instead, the operator IS [NOT] [$]NULL is used as shown in the following example:
Salary is $null
Salary is null
Salary is not $null
Salary is not null
Note: You can write $Null without the dollar sign.
Example
if Salary is $null
let Salary = StartingSalary
... commands ...
endif
See Also
$False
$True
Checks a value to see if it is $Null.
Syntax
expression IS [NOT] $NULL
Parameters
| expression | Any expression. |
Return Value
Logical.
Comments
An IS $NULL comparison is logically true if expression is valueless (unassigned). If expression has been assigned any value (including the null string), then the comparison is logically false.
An IS NOT $NULL comparison is logically true if expression has been assigned any value (including the null string). If expression is valueless (unassigned), then the comparison is logically false.
$NULL can also be written as NULL.
Example
To find all employees whose records have no value recorded in the Age field, and then change that value to 39, enter
change all Employees where Age is $Null let Age = 39
See Also
About Boolean Expressions
About Conditional Expressions
Controls the evaluation of other expressions.
Syntax
expression1 WHERE expression2
Parameters
| expression1 | A value expression. |
| expression2 | A logic expression using conditional and Boolean operators. |
Return Value
If expression2 is logically true, the value of expression1; otherwise, $Null.
Example
let i = { 1 where Age < 10,
2 where Age between 10 and 30,
4 where Age > 50, 3 }
Assigns a value to the variable i based on Age.
compute Employees evaluate
(let TotSal = $total(Salary where LastName = “Smith”)
Finds the total of the salaries of all employees named Smith.
See Also
About Boolean Expressions
About Conditional Expressions
Conventions
LET
Special Expression Formats
Matches an expression to a pattern.
Syntax
expression [NOT] LIKE pattern
Parameters
| expression | An expression that evaluates to a character string. |
| pattern | An expression that evaluates to a character string. Pattern must consist of combinations of letters, digits, symbols, and the special wildcard characters % and _. |
Return Value
Logical.
Comments
A LIKE comparison is logically true if expression matches patterns.
A NOT LIKE comparison is logically true if expression fails to match pattern.
Example
compute Employees where LastName like "_a%n"
Processes all records whose LastName values are three characters or more, the second character being an a, and the last character being an n.
find Parts where PartNo like "_ _\_%"
Finds all parts whose part numbers have an underscore as the third character.
find Parts where PartDesc like "%\\%"
Finds parts whose part description contains a backslash.
See Also
About Conditional Expressions
English 
Portuguese