Set Specification (ISQL)

5 out of 5 stars

1 rating

5 Stars		100%
4 Stars		0%
3 Stars		0%
2 Stars		0%
1 Stars		0%

Identifies particular records in an SQL database.

Syntax

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

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

Parameters

object	The name of an EntitySet, relationship, form, structured application document, or result set. Role names can be used for EntitySets and relationships.
expr1	A value expression. Specifies how the selected records are to be grouped.
expr2	A logic expression. Limits the groups to be kept in the result set.
expr3	A value expression. Specifies the sort keys for the result set.
ASC or DESC	ASC 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.
component	Out 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.

Set Specification (ISQL)