Table of Contents

NAME

select -- retrieve instances from a class

SYNOPSIS

select [distinct]
expression1 [as attr_name-1]
{, expression-1 [as attr_name-i]}
[into table classname]
[from from-list]
[where where-clause]
[group by attr_name1 {, attr_name-i....}
[order by attr_name1
   [using op1 ] {, attr_namei [using opi ] }

DESCRIPTION

Select will get all instances which satisfy the qualification, qual, compute the value of each element in the target list, and either (1) return them to an application program through one of two different kinds of portals or (2) store them in a new class.

If into table class name is specified, the result of the query will be stored in a new class with the indicated name.

The order by clause allows a user to specify that he wishes the instances sorted according to the corresponding operator. This operator must be a binary one returning a boolean. Multiple sort fields are allowed and are applied from left to right.

The target list specifies the fields to be retrieved. Each attr_name specifies the desired attribute or portion of an array attribute. Thus, each attr_name takes the form class_name.att_name
or, if the user only desires part of an array, --
--Specify a lower and upper index for each dimension
--(i.e., clip a range of array elements)
--
class_name.att_name[lIndex-1:uIndex-1]..[lIndex-i:uIndex-i]

--
--Specify an exact array element
--
class_name.att_name[uIndex-1]..[uIndex-i]
where each lIndex or uIndex is an integer constant.

When you retrieve an attribute which is of a complex type, the behavior of the system depends on whether you used "nested dots" to project out attributes of the complex type or not. See the examples below.

You must have read access to a class to read its values (see grant/revoke(l) .

EXAMPLES

--
--Find all employees who make more than their manager
--
select e.name
from emp e, emp m
where e.mgr = m.name
and e.sal > m.sal
--
--Retrieve all fields for those employees who make
--more than the average salary
--
select avg(sal) as ave
into table avgsal from emp;
--
--Retrieve all employee names in sorted order
--
select distinct name
from emp
order by name using <
--
--Retrieve all employee names that were valid on 1/7/85
--in sorted order
--
selec name
from emp['January 7 1985'] e
order by name using <
--
--Construct a new class, raise, containing 1.1
--times all employee's salaries
--
select 1.1 * emp.salary as salary
into tables raise
from emp

SEE ALSO

insert(l) , close(l) , create table(l) , fetch(l) , update(l) .

BUGS

Select into does not delete duplicates.

If the backend crashes in the course of executing a select into, the class file will remain on disk. It can be safely removed by the database DBA, but a subsequent select into to the same name will fail with a cryptic error message about `BlockExtend'.

Table of Contents