SELECT

Use the SELECT statement to retrieve information from a database.

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.

The SELECT statement uses the following syntax:

Syntax

SELECT [ ALL | DISTINCT ]
   [TOP n] { * | { table_name | alias} * 
   [ , { table_name| alias} * ]... 
     | expr [[ AS ][ ' ]column_title
   [ ' ]][, expr [[ AS ][ ' ]column_title
   [ ' ]]]...}
 FROM table_ref[, table_ref ]...
   [{ NO REORDER }]
   [ WHERE search_condition]
   [ GROUP BY [ table]column_name[, [ table]column_name ]...
   [ HAVING search_condition] ;
   [ORDER BY ordering_condition]  [WITH locking_hints]  
   [FOR UPDATE update_condition]
   [WITH(NOLOCK)];

The SELECT statement provides you with countless ways to retrieve and analyze the data in your database. Queries vary from simple to sophisticated. They can retrieve information from a single column of a single table, or they can retrieve data meeting specific conditions from many columns across many tables.

The following statements offer just a few examples of how the SELECT statement can be used to create queries.

In the following example, the simple SELECT statement retrieves all columns from the Customer table.

SELECT * FROM Customer;

In the following example, the statement is easily modified to identify columns from which the data will be retrieved.

Select CustNum, Name, City FROM Customer;

The simple SELECT - FROM combination can even be used to retrieve a single set of results from multiple tables. The following example retrieves the customer and order information from both the Customer and Order tables.

SELECT Customer.CustNum, Customer.Name, Order.OrderNum, Order.OrderDate 
     FROM Customer, Order; 

The WHERE clause can be used to further refine your query. In the following example, the FROM clause uses the WHERE, GROUP BY, and HAVING conditions to create a highly specific query statement. The query returns the customer number and number of orders for all customers who had more than 10 orders before March 31, 2008.

SELECT CustNum, COUNT(*) 
     FROM Order
     WHERE OrderDate < '3/31/08'
     GROUP BY CustNum
     HAVING COUNT (*) > 10 ;

The WHERE clause limits a query to retrieving specified rows based upon a search condition. The GROUP BY clause produces a summary query in which similar rows are grouped together.The HAVING clause further restricts the GROUP BY clause by allowing only those groups that have been specified by a search condition.

Assuming the user is mapped to a DBA or a super-tenant, the following example 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.

SELECT
    tenantName_tbl (c) as ten_name,
    c.name as c_name,
    COUNT (*)
FROM
    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
GROUP BY
    ten_name, c_name;

Assuming the user is mapped to a DBA or a super-tenant, the following example directs the SELECT statement to join a multi-tenant table to another multi-tenant table associated with a multi-tenant group.

When you join a multi-tenant table to a table with a multi-tenant group, as shown in this example, the data rows in the group are displayed exactly once for the entire set of tenants in the group.

SELECT * FROM Pub.mtcustomer AS c
INNER JOIN pub.mtorder AS o
ON tenantId_tbl (c) = tenantId_tbl (o)
INNER JOIN pub.mtorderline AS ol
ON c.custnum = o.custnum
AND tenantId_tbl (o) = tenantId_tbl (ol)
;

Alternately, you can view multi-tenant data on a tenant-by-tenant basis, including data from a multi-tenant group. Viewing data from a group on a tenant-by-tenant basis means that the data rows in the group are displayed once for each tenant in the group.

This can be accomplished by joining to the OpenEdge schema table _Tenant. In the following example, assume that the table pub.mtOrder is associated with a multi-tenant group for some its tenants' data.

SELECT * FROM pub."_Tenant" AS t
INNER JOIN pub.mtorder AS o
ON t."_Tenant" = tenantId_tbl (o)
INNER JOIN pub.mtorderline AS ol
ON c.custnum = o.custnum
AND tenantId_tbl (o) = tenantId_tbl (ol)
;

OpenEdge SQL can access all databases objects created with ABL. ABL can access SQL-created objects, but they must contain data types recognized by ABL and must reside in the PUB schema of an OpenEdge RDBMS.

For more information on the SELECT statement, see OpenEdge SQL Reference.