Category: Subcommands

USING

USING

In a set specification, qualifies a relationship.

Syntax

relationship («USING obj1 AS obj2»)

Parameters

relationshipThe name of the relationship being qualified.
obj1The name of an object used elsewhere in the set specification in which relationship appears. Must be the name or role name of an EntitySet, relationship, form, or application document. Must refer to same underlying object as obj2 (e.g., one is a role name for the other).
obj2The name of one of the objects associated by relationship. Must be the name or role name of an EntitySet, relationship, form, or application document. Must refer to same underlying object as obj1 (e.g., one is a role name for the other).

Comments

When a set specification includes a relationship, two principles are used to determine the validity of EntitySets associated by the relationship:

  1. If the relationship condition contains an EntitySet name, then that EntitySet name, or any role for it, may be used in set specification involving the relationship.
  2. If the relationship condition contains a role name, only that role name may be used in a set specification involving the relationship.

The USING subcommand helps to eliminate any ambiguity as to which objects are to be associated in a set specification. Ambiguity may develop when an object, or role for that object, appears more than once in a complex set specification.

Example

list all Employees WorkFor
Managers WorkFor (using Managers as Employees
using Bosses as Managers)
Bosses
Lists all bosses (managers of managers).
list all Employees WorkFor Managers
WorkIn (using Managers as Employees) Departments

The USING subcommand associates the departments with the managers, not with the employees.

See Also

– (Dynamic Rename)

COMPLETE

UNRELATED

Set Specification

Set Specification

Identifies particular records in an Zim database.

Syntax

object [WHERE clause] [SORTED BY clause] [KEEP clause]

Parameters

objectThe name of an EntitySet, relationship, form, application document, or result set. Role names can be used for EntitySets or relationships. Any number of objects can be specified, provided that they are meaningful in context.

Comments

A set specification (sometimes called a setspec) is a statement that uses EntitySets, relationships, forms, application documents, and result sets as the source of the set being declared. Each object in the set specification is called a component. (Result sets can represent more than one component.)
A set specification can also include a condition that limits record selection (WHERE clause), a sorting statement (SORTED BY clause), and a component projection list that limits the number of components in the resulting set (KEEP clause).
Any number of objects can be declared. But, if you declare more than one object, then you must declare the relationships through which records in those objects are associated.
Each object can be further qualified using any valid combination of the following subcommands:
-> (Dynamic rename), COMPLETE, UNRELATED, USING
and in particular circumstances:
INTERSECT, MINUS, UNION
Set specifications are processed, in order, from left to right.

Example

list all Employees

The simplest type of set specification. The set consists of all of the records in the EntitySet Employees.

list all Employees where LastName = “Smith”

Includes a condition for record selection.

list all Employees sorted by EmpNum

Includes a sorting subcommand.

list all Employees where LastName = “Smith” sorted by EmpNum

Combines a condition and a sort.

list all Employees WorkIn Departments

The relationship that defines the association (WorkIn) must be included because two EntitySets are being declared. All associated records in Employees and Departments are included.

list all Employees WorkIn Departments keep Departments

Adds a component projection list. Of associated records in Employees WorkIn Departments, only the (unique) records from Departments are included in the set.

list all Employees (unrelated) WorkIn Departments

The subcommand UNRELATED modifies the relationship; all records in Employees that are unassociated with Departments are included.

See Also

Set Specification (ISQL)

-> (Dynamic Rename)

Dynamic Rename (->)

Overview

Dynamic renaming (->) is a feature used in command structures to rename components within a set specification. This allows for flexible manipulation of data sets, enabling components to be used in different roles across various commands.

Syntax

objname (oldcomponentname -> newcomponentname)
  • objname: The name of a set, an EntitySet, a relationship, or a role. There must be at least one component in objname.
  • oldcomponentname: The existing name of a component in objname.
  • newcomponentname: The new name to be given to the specified component of objname.

Detailed Explanation

When creating a result set, the structure of that set reflects the component names or role names found in the original set specification. If you want to use the result set in a subsequent command and have a component of the set used in a different role, dynamic renaming (->) enables you to rename the component.

Both oldcomponentname and newcomponentname must refer to the same underlying object.

Examples

Example 1: Renaming Employees to Managers

find Employees WorkFor Managers where LName = Smith 
keep Employees -> set1
find Employees WorkFor set1 (Employees -> Managers) 
keep Employees -> set1

In this example, the component Employees is dynamically renamed to Managers in the second command. This allows the Employees component to be used in the role of Managers.

Example 2: Loop Example

find Managers -> MSet
while
  find MSet (Managers -> Employees) WorkFor Managers keep Managers
  if $setcount > 0
    find -> MSet
  else
    break
  endif
endwhile
list all MSet

In this loop, each usage of the Managers component of MSet is dynamically renamed to Employees to select the top managers from an EntitySet of employees. This iterative process continues until no more managers are found.

Comments

  • Flexibility: Dynamic renaming provides flexibility in how components are used in different roles across commands. This is particularly useful in complex queries and data manipulations.
  • Consistency: Ensures that the underlying object remains consistent even when its role or name changes. This helps maintain the integrity of the data set while allowing for dynamic adjustments.

Practical Applications

Dynamic renaming can be used in various scenarios, such as:

  • Data Analysis: Renaming components to fit different analytical roles.
  • Database Management: Adjusting roles of components for efficient querying and data retrieval.
  • Software Development: Implementing dynamic role changes in algorithms and data structures.

Why Use Dynamic Renaming?

1. Flexibility in Data Manipulation

Dynamic renaming allows users to adapt the roles of components within a data set on-the-fly. This flexibility is crucial when dealing with complex data structures or when the same data needs to be viewed or analyzed from different perspectives.

2. Simplifying Complex Queries

In scenarios where multiple roles or relationships exist within a data set, dynamic renaming can simplify queries. By renaming components, users can avoid writing redundant or overly complex commands, making the code more readable and maintainable.

3. Enhanced Data Analysis

Analysts often need to pivot data to gain different insights. Dynamic renaming enables them to reassign roles to components, facilitating various analytical approaches without altering the underlying data structure.

4. Efficient Resource Management

In database management, dynamic renaming can help optimize resource usage. By reusing components in different roles, it reduces the need to create multiple copies of the same data, thereby saving storage and processing power.

5. Improved Algorithm Implementation

For software developers, dynamic renaming can be particularly useful in implementing algorithms that require role changes. It allows for more dynamic and adaptable code, which can handle a wider range of scenarios without significant modifications.

Conclusion

Dynamic renaming is a versatile tool that enhances the flexibility, efficiency, and clarity of data manipulation and analysis. It allows users to adapt to changing requirements and complex data relationships seamlessly.

Set Specification (ISQL)

Identifies particular records in an SQL database.

Syntax

Object [WHERE clause] [GROUP BY expr1] [HAVING expr2]

[ORDER BY expr3 [ASC|DESC]] [KEEP component]

Parameters

objectThe name of an EntitySet, relationship, form, structured application document, or result set. Role names can be used for EntitySets and relationships.
expr1A value expression. Specifies how the selected records are to be grouped.
expr2A logic expression. Limits the groups to be kept in the result set.
expr3A value expression. Specifies the sort keys for the result set.
ASC or DESCASC indicates sorting in ascending order (i.e., 0-9, A-Z); DESC, in descending order (i.e., 9-0, Z-A). ASC is the default.
componentOut of all the specified objects, the components to be retained in the result set.
A KEEP clause cannot be used if a GROUPED BY or HAVING clause already appears in the set specification.

Comments

An SQL set specification (sometimes called an SQLsetspec) is a statement that uses EntitySets, relationships, forms, structured application documents and result sets to identify particular records in an SQL database. Each object in the set specification is called a component. (Result sets can represent more than one component.)
SQL set specifications are used only within the SQL commands DELETE FROM, INSERT, SELECT, and UPDATE.
Any number of objects can be declared. But, if you declare more than one object, then you must declare the relationships through which records in those objects are associated.
An SQL set specification can also include a condition that limits record selection (WHERE clause), a grouping statement (GROUP BY clause), a group condition that limits group selection (HAVING clause), a sorting statement (ORDERED BY clause), and a component projection list that limits the number of components in the resulting set (KEEP clause).
The WHERE clause can also contain another SELECT statement and OUTER JOIN references using “*=” for LEFT OUTER JOIN or “=*” for RIGHT OUTER JOIN (see examples).
Each object can be further qualified using any valid combination of the following subcommands:
-> (Dynamic rename) or USING
and in particular circumstances:
INTERSECT, MINUS, UNION

Example

select * from Employees where LastName=”Smith”

The set specification includes a WHERE condition for record selection.

SELECT * FROM Employees WHERE Salary > (SELECT Salary FROM Employees WHERE JobPosition = “THE BOSS”)

SELECT Ents.EntName,Fields.OwnerName FROM Ents,Fields WHERE Ents.EntName *= Fields.Ownername

This selects all EntitySets and having or not having corresponding Fields in the same way the

COMPLETE clause does in a FIND statement.

SELECT Ents.EntName,Fields.OwnerName FROM Ents,Fields WHERE Ents.EntName *= Fields.Ownername AND

Fields.Ownername is $NULL

This selects all EntitySets not having corresponding Fields in the same way the UNRELATED

clause does in a FIND statement.

See Also

DELETE FROM

INSERT

SELECT

Set Specification

UPDATE

EVALUATE

EVALUATE

Evaluates expressions while the main command processes a set of records.

Syntax

EVALUATE «expression»

Parameters

expressionAny valid expression, but usually an assignment using LET and an aggregate function. If expression is complex, it must be enclosed in parentheses.

Example

change all Employees where LastName = Smith
let Salary = 1.1 * Salary
evaluate (let ExtraCost = $ total(Salary * 0.1))

Determines how much extra money is needed after all employees named Smith are given a 10% pay raise.

let RegTot[1]=0 RegTot[2]=0 RegTot[3]=0
compute Sales
evaluate (let RegTot[RegNum] = RegTot[RegNum] + SalesAmt)

Calculates total sales by region assuming three regions were served by the same sales force.

See Also

ADD

CHANGE

COMPUTE

DELETE

FIND

LET

LIST

REPORT FROM

KEEP

KEEP

Keeps (retains) some components in a result set while discarding others.

Syntax

KEEP «component»

Parameters

component

The name of a component found among the objects declared in a set specification. Any number of components can be specified, provided they appear among the declared objects.

Comments

In certain instances, you want to select records from several objects based on their relationships, but to keep data from only some of those objects. The KEEP subcommand enables you to specify the components whose data is to be kept in the result set.

The specified components of the set specification are kept. Components not kept are discarded, and duplicate records are removed from the set being specified.

Example

find all Employees WorkIn Department
  where LastName = “Jones” keep Departments

The department information is kept; all other components are discarded. If the KEEP subcommand were omitted, department information would be repeated for any department where more than one employee named Jones works.

 

See Also

ADD

CHANGE

COMPUTE

DELETE

FIND

INSERT

LIST

REPORT FROM

SELECT

UPDATE

-> (Result Set)

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

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

  1. 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.
  2.  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.
  3. 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

UNION

Combines the members of two or more result sets into a single set.

Syntax

set1 UNION set2

Parameters

set1The name of a result set that resulted from the execution of a set-producing command.
Set1 and set2 must have the same component structure.
set2The 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

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

SORTED BY

Sorts the members of the set produced by the main command.

Syntax

SORTED BY «expression [ASCENDING|DESCENDING]»

Parameters

expressionAn expression that identifies a field to be used as a sort key. Complex expressions must be enclosed in parentheses.
ASCENDING or DESCENDINGSpecifies 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

en_CAEnglish
pt_BRPortuguese en_CAEnglish