| table |
The table name from which to select data. When data is being selected from encrypted tables, the table reference can include the three-part encryption key, enclosed in angled brackets, appended to the table name. The SET ENCRYPTION command allows a default encryption key to be defined. If the key is not included in the , this default key will be used. If the default key is not the correct key for the table, an error will be given. If no default key is active, a dialog box will be displayed in Recital Terminal Developer to allow the user to enter the key.
| view |
The name of a view defined with the CREATE VIEW statement
|
| {OJ … OUTER JOIN …} |
Specifies the join type as left outer. This will return all the rows from the left table and matching rows from the right or a null row if no match is found.
|
| {OJ … LEFT OUTER JOIN …} |
Specifies the join type as left outer. This will return all the rows from the left table and matching rows from the right or a null row if no match is found.
|
| {OJ … RIGHT OUTER JOIN …} |
Specifies the join type as right outer. This will return all the rows from the right table and the matching rows from the left or a null row if no match is found.
|
| {OJ … FULL OUTER JOIN …} |
Specifies the join type as full outer. This will return all the rows from both tables.
|
| Keywords |
Description
|
| table2 |
The name of the joined table in the query. When data is being selected from encrypted tables, the table2 reference can include the three-part encryption key, enclosed in angled brackets, appended to the table name. The SET ENCRYPTION command allows a default encryption key to be defined. If the key is not included in the <table2>, this default key will be used. If the default key is not the correct key for the table, an error will be given. If no default key is active, a dialog box will be displayed in Recital Terminal Developer to allow the user to enter the key.
|
| ON <exp> = <exp> |
The expression used to JOIN the two tables together.
|
| INNER JOIN |
Specifies the join type as inner. An inner join names the linking criterion used to find matches between the two tables. Only rows for which a match is found in both tables are returned.
|
| OUTER JOIN |
Specifies the join type as outer. An outer join takes two tables and displays all the rows from one table and the matching rows from the other or a null row if no matches are found.
|
| LEFT [OUTER] JOIN |
Specifies the join type as left outer. This will return all the rows from the left table and matching rows from the right or a null row if no match is found.
|
| RIGHT [OUTER] JOIN |
Specifies the join type as right outer. This will return all the rows from the right table and the matching rows from the left or a null row if no match is found.
|
| CROSS JOIN |
Specifies the join type as cross join. This will return a Cartesian product: all combination of rows.
|
| FULL [OUTER] JOIN |
Specifies the join type as full outer. This will return all the rows from both tables.
|
| <nested select> |
An additional SELECT statement.
|
ON .column = <table2>.column
| The expression used to JOIN the two tables together.
|
| WHERE |
This restricts the rows selected to those for which the condition is TRUE. If this clause is omitted than all rows are returned. The condition can be a SELECT sub-query.
|
| GROUP BY |
Groups the selected rows based on the value of an expression, the column name or number for each row and returns a single row of summary information for each group. The default, ASC, returns the data in ascending order, specifying DESC returns the data in descending order.
|
| HAVING |
Restricts the groups of rows returned to those groups for which the specified condition is TRUE. If you omit this clause then all rows are returned.
|
| ORDER BY |
Orders rows based on the value returned by an expression, a column name or number. The default, ASC, returns the data in ascending order, specifying DESC returns the data in descending order.
|
| FOR UPDATE |
Locks the selected rows.
|
| INTO [,…] |
Specify data variables to receive the data retrieved by the select statement. The select statement can only be a singleton select. The data variables will be created if they do not exist and overwritten if they do.
|
| INTO ARRAY
<array-name>||Specify an array to receive the data retrieved by the select statement. The array is automatically created, so need not be pre-defined.
|
| Keywords |
Description
|
| INTO CURSOR <cursor-name> |
Specify a cursor to receive the data retrieved by the select statement. This saves the data into a temporary table in a workarea. The Recital/4GL SELECT command can be used to select the workarea for processing with Recital/4GL commands. The NOFILTER keyword is used for creating a cursor that can be used in subsequent queries. The READWRITE keyword is used to create a temporary modifiable cursor.
|
| TABLE <table-name> |
Specify a table to receive the data retrieved by the select statement. The table's database and a long name can optionally be specified using the DATABASE and NAME clauses respectively.
|
| SAVE AS <table-name> |
Specify a table name to be created and populated with the result of the select statement.
|
| ADO>]]] |
ADO>.
|
| TO FILE [DELIMITED] |
Saves the results to the specified text file. If the DELIMITED keyword is included, the results are written out in delimited format.
|
| TO PRINTER |
Sends the results to the currently defined printer. The optional PROMPT keyword is used to display a print dialog before printing.
|
| TO SCREEN |
Sends the results to the main screen or active window.
|
| PREFERENCE |
The PREFERENCE clause is used to save BROWSE window preferences.
|
| NOCONSOLE |
PRINTER) also being displayed on the screen.
|
| PLAIN |
The PLAIN keyword is used to disable the display of column headings.
|
| NOWAIT |
The NOWAIT keyword is used to continue program execution immediately after BROWSE window display instead of when the BROWSE window is closed.
|
| UNION [ALL] |
Combines the end result of the main SELECT statement with a secondary <nested select> SELECT statement. The ALL keyword specifies that duplicates should be retained.
|
Example
// Select all rows, including duplicates, from an encrypted table
EXEC SQL
SELECT ALL *
FROM enctab<key_1,key2,key_3>;
// Select "last_name" column from rows with a unique "last_name"
EXEC SQL
SELECT DISTINCT last_name FROM customer;
// Select "last_name" column from unique rows
EXEC SQL
SELECT DISTINCTROW last_name FROM customer;
// Select first 10 rows
EXEC SQL
SELECT TOP 10 * FROM accounts;
// Select first 50% of the rows
EXEC SQL
SELECT TOP 50 PERCENT * FROM accounts;
// Crystal Reports / ODBC style JOINS: LEFT OUTER, RIGHT OUTER, FULL OUTER
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
FROM
{OJ "customer" customer LEFT OUTER JOIN "accounts" accounts
ON customer.account_no = accounts.account_no};
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
FROM
{OJ "customer" customer RIGHT OUTER JOIN "accounts" accounts
ON customer.account_no = accounts.account_no};
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
FROM
{OJ "customer" customer FULL OUTER JOIN "accounts" accounts
ON customer.account_no = accounts.account_no};
// JOINS: INNER, LEFT OUTER, RIGHT OUTER, CROSS, FULL
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
FROM customer
INNER JOIN accounts
ON customer.account_no = accounts.account_no;
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
FROM customer
LEFT OUTER JOIN accounts
ON customer.account_no = accounts.account_no;
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
FROM customer
RIGHT OUTER JOIN accounts
ON customer.account_no = accounts.account_no;
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
FROM customer
CROSS JOIN accounts;
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
FROM customer
FULL OUTER JOIN accounts;
// JOINs with nested SELECTs
EXEC SQL
SELECT contactname FROM customers WHERE customerid IN (SELECT customerid FROM orders WHERE orderdate = {07/19/1996});
EXEC SQL
SELECT shipname FROM orders,customers WHERE orders.customerid = customers.customerid AND employeeid = (SELECT max(employeeid) FROM orders WHERE orderdate = {07/19/1996}) order by shipname;
EXEC SQL
SELECT contactname FROM customers WHERE ctod("07/19/1996") > ANY (SELECT orderdate FROM orders WHERE customers.customerid = orders.customerid);
EXEC SQL
SELECT orders.customerid FROM orders WHERE 30 > ALL (SELECT sum(quantity) FROM order_details WHERE orders.orderid = order_details.orderid);
EXEC SQL
SELECT orderid,customerid
FROM orders as o1
WHERE 2 < (SELECT quantity
FROM order_details as i1
WHERE o1.orderid = i1.orderid AND i1.productid = 71);
EXEC SQL
SELECT lastname FROM employees WHERE exists
(SELECT * FROM orders WHERE employees.employeeid = orders.employeeid AND orderdate = CTOD("11/11/1996")) ;
EXEC SQL
SELECT lastname FROM employees WHERE not exists
(SELECT * FROM orders WHERE employees.employeeid = orders.employeeid AND orderdate = CTOD("11/11/1996")) ;
EXEC SQL
SELECT companyname,(select MAX(orderid) FROM orders as o1 WHERE o1.customerid = c1.customerid) FROM customers as c1;
// Multiple JOINs
EXEC SQL
SELECT customer.account_no, customer.state, state.descript, accounts.ord_value
FROM customer RIGHT OUTER JOIN accounts
ON customer.account_no = accounts.account_no,
customer INNER JOIN state
ON customer.state = state.state
ORDER BY account_no;
// Select account number and order value details for Massachusetts customers
EXEC SQL
SELECT account_no, ord_value
FROM accounts
WHERE account_no in (SELECT account_no FROM customer WHERE state = 'MA')
ORDER BY account_no;
// Select all overdue accounts with 15% commission in sorted "last_name" order.
EXEC SQL
SELECT last_name, zip, balance, balance*1.15
FROM customer
WHERE balance > 0
ORDER BY last_name;
// Select total and average balance for all overdue accounts, grouped by "limit"
EXEC SQL
SELECT SUM(balance), AVG(balance)
FROM customer
WHERE balance > 0
GROUP BY limit;
// Select total and average balance for all overdue accounts, grouped by "limit" with column aliases
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
FROM customer
WHERE balance > 0
GROUP BY limit;
// Select total and average balance for all overdue accounts, grouped by "limit" with column aliases
// For Massachusetts customers only
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
FROM customer
WHERE balance > 0
GROUP BY limit
HAVING state = "MA";
// Save into an array
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
FROM customer
WHERE balance > 0
INTO ARRAY temp;
// Create a cursor
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
FROM customer
WHERE balance > 0
INTO CURSOR temp;
// Save as a table
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
FROM customer
WHERE balance > 0
INTO TABLE temp DATABASE mydbc;
//or
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
FROM customer
WHERE balance > 0
SAVE AS temp;
// Save in Microsoft® ActiveX® Data Objects XML format
// Any XML files created in the ADO format can be loaded
// with the Open method of the ADO Recordset object.
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
FROM customer
WHERE balance > 0
SAVE AS XML temp.xml FORMAT ADO;
// In Visual Basic the file can then be loaded:
// Set adoPrimaryRS = New Recordset
// adoPrimaryRS.Open "temp.xml"
// Save in text file format
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
FROM customer
WHERE balance > 0
TO FILE temp.txt;
// Select all customer accounts that have an outstanding balance or are based in Massachusetts
EXEC SQL
SELECT account_no
FROM customer
WHERE state = 'MA'
UNION SELECT account_no
FROM accounts
WHERE balance > 0
ORDER BY account_no;
Products
Recital Database Server, Recital Mirage Server, Recital Terminal Developer
|
|
