SELECT
Function
The SELECT statement obtains data from the database according to the specified conditions.
If your ADABAS file uses super or sub descriptors,
Adabas Performance Tuning contains information to minimize search time.
Syntax
QUERY EXPRESSION [ORDER BY CLAUSE] [UPDATABILITY CLAUSE]
ORDER BY CLAUSE syntax:
ORDER BY [integer_constant | column_specification | expression] [ | ASC | DESC]
UPDATABILITY CLAUSE syntax:
FOR UPDATE [OF column_specification[,]]
where
query expression | |
order_by_clause | The specification of a user-defined ordering of the resultant table. Otherwise the resultant table is not ordered. |
updatability_clause | The explicit indication that this cursor is to be used in conjunction with either an UPDATE and/or DELETE WHERE CURRENT OF CURSOR statement. |
Description
The SELECT statement is used to obtain data from the database. Please refer to the description of a query expression or query specification (Common Elements) for information on the processing of a SELECT statement.
When submitted either dynamically the statement must be associated with a PREPARE statement and an associated dynamic cursor. The statement may then select more than one row.
When used interactively, the statement may again select more than one row. The use of the INTO clause is not permitted.
Limitations
The use of an ORDER BY clause is only valid within a dynamic or interactive SELECT statement. Its use enables the resultant table to be sorted in a user-defined sequence.
The use of the FOR UPDATE clause is only valid within a dynamic or interactive SELECT statement.
ANSI Specifics
The use of a FOR UPDATE clause is not supported.
Examples
Simple Select Example:
SELECT customername, customerid FROM customers WHERE customerid = "ALCAO"
Inner Join Example:
SELECT customername, orderid FROM customers c, orders o WHERE c.customerid = o.customerid
Outer Join Example:
SELECT customername, orderid FROM customers c, orders o WHERE c.customerid *=o. customerid
Sub-query as a Table Example:
SELECT * FROM (SELECT customername, customerid FROM customers WHERE customerid = "ALCAO")
GROUP BY Example:
SELECT customername, sum(o.productquantity * p.productprice) FROM customers c, orders o, products p WHERE c.customerid = o.customerid AND o.productid = p.productid GROUP BY c.customername
GROUP BY Ordinal Example:
SELECT customername, sum(o.productquantity * p.productprice) FROM customers c, orders o, products p WHERE c.customerid = o.customerid AND o.productid = p.productid GROUP BY 1
ORDER BY Example:
SELECT customername, sum(o.productquantity * p.productprice) FROM customers c, orders o, products p WHERE c.customerid = o.customerid AND o.productid = p.productid ORDER BY c.customername
ORDER BY Ordinal Example:
SELECT customername, sum(o.productquantity * p.productprice) FROM customers c, orders o, products p WHERE c.customerid = o.customerid AND o.productid = p.productid ORDER BY 1
SELECT Sub-Query:
SELECT * from (SELECT * FROM CONNXDataSync.datasync.TableSynchronizations a, CONNXDataSync.datasync.TableSynchronizations b where a.tableid = b.tableid)