Try OpenEdge Now
skip to main content
SQL Reference
SQL Reference : OpenEdge SQL Statements : SELECT


Selects the specified column values from one or more rows contained in the tables or views specified in the query expression. The selection of rows is restricted by the WHERE clause. You can also use clauses like GROUP BY, HAVING, ORDER BY with the SELECT statement. These clauses are described in detail in the further sections. To limit the number of rows that are returned, you can implement a query paging solution using the OFFSET and FETCH clauses. The temporary table derived through the clauses of a select statement is called a result table.
When selecting rows in a multi-tenant table, a regular tenant can only view the rows in its partition, but a DBA or a super-tenant can view all the tenant partitions in the multi-tenant tables being accessed.
A super-tenant can also view a list of tenant IDs and tenant names that are associated with data from a tenant partition or from a group partition, by using the tenantid_tbl() and the tenantName_tbl() functions, respectively.
These functions logically evaluate to the set of IDs and names of the tenants that are associated with a tenant partition or with a group partition.They also enable the query to specify the tenants for which data should be selected.
When selecting rows from a partitioned table, the SELECT statement returns data from all the partitions created for the table as determined by the predicates (restrictions) in the statement.


|{table_name|alias} * [ , {table_name| alias} * ]...
expr [[ AS ][ ' ] column_title [ ' ]]
[, expr [[ AS ][ ' ] column_title [' ]]]...

FROM table_ref [, table_ref]...[{ NO REORDER }] [ WITH (NOLOCK )]
WHERE search_condition][ GROUP BY [ table ]column_name
,[table]column_name ]...
HAVING search_condition]

 [ORDER BY ordering_condition] 
 [OFFSET offset_value {ROW | ROWS } 
   [FETCH {FIRST | NEXT}fetch_value {ROW | ROWS} ONLY ]] 
 [WITH locking_hints] 
 [FOR UPDATE update_condition];
Note: The SELECT statement does not the support the use of the FOR UPDATE caluse and the ORDER BY clause or any aggreagte function in the same staement. For example, the statement SELECT * FROM table ORDER BY field1 FOR UPDATE; will return an error.


See the .
TOP nsearch_condition
See the .
FROM table_list
See the .
WHERE search_condition
See the .
GROUP BY grouping_condition
See the .
HAVING search_condition
See the .
ORDER BY ordering_condition
See the .
OFFSET offset_value
See the .
See the .WITH locking_hints
See the .
FOR UPDATE update_condition
See the .


The following example displays the different forms of the SELECT statement:
The example below illustrates the use of SELECT statement.
SELECT * FROM pub.customer;
Assuming the user is mapped to a DBA or a super-tenant, the example below directs the SELECT statement to join three multi-tenant tables.
When joining three multi-tenant tables, the DBA or the super-tenant must make sure that data from one tenant, pub.mtcustomer, is joined only with the data owned by the same tenant in the other tables, pub.mtorder and pub.mtorderline; especially when primary keys, custnum and ordernum, are unique for a tenant, but not unique across all the tenants.
    tenantName_tbl (c) as ten_name, as c_name,
    COUNT (*)
    Pub.mtcustomer AS c
    INNER JOIN pub.mtorder AS o
    ON tenantId_tbl (c) = tenantId_tbl (o)
    AND c.custnum = o.custnum
    INNER JOIN pub.mtorderline AS ol
    ON tenantId_tbl (o) = tenantId_tbl (ol)
    AND o.ordernum = ol.ordernum
    ten_name, c_name;
* COLUMN_LIST clause
* TOP clause
* FROM clause
* WHERE clause
* GROUP BY clause
* HAVING clause
* ORDER BY clause
* OFFSET and FETCH clauses
* WITH clause
* FOR UPDATE clause
* Authorization
* Related Statements