800.TXT
     =====================================================================
     Building Sub-SELECTs in QBE
     =====================================================================
     PRODUCT:  R:BASE                  VERSION:        5.X
     =====================================================================
     AREA   :  Select                  CATEGORY:       Programming
     =====================================================================
 
    A sub-SELECT is used to display rows of data from one table based on 
    conditions placed on data in a second table. A sub-SELECT returns a 
    list of data values to compare to data in the query table. For 
    example, you want to review sales information for customers in the 
    state of California. You don't need to see any customer information, 
    just the sales data,  but the sales data is stored in the Transmaster 
    table and the state is stored in the Customer table. To query the data 
    from the Transmaster table with the qualifying conditions on data in 
    the Customer table, use a sub-SELECT. The Transmaster and Customer 
    tables are linked by the customer identification number, custid. The 
    query selects rows from the Transmaster table where the custid column 
    is compared to the list of custid values selected from the Customer 
    table.
 
     In prior versions of R:BASE, you could only create a sub-SELECT query 
     from the R> prompt. In R:BASE 5.0, the following two new features 
     allow you to create a sub-SELECT query in Query by Example (QBE).
 
       You can open more than one QBE window at a time
       You can edit a WHERE clause in the Where Builder
 
     The following steps show you how to create a sub-SELECT query using 
     the Concomp sample database.
 
     1. Start QBE_Choose Tools:QBE or click the QBE button on the tool 
     bar.
 
     2. Add the Transmaster table; this is the query table. Data is 
     displayed from this table. The WHERE clause of this query uses the 
     sub-SELECT to determine the rows of data to display.
 
     3. Select the transid, transdate, and invoicetotal columns. The query 
     displays these columns.
 
     4. Open a second QBE window and select the Customer table, the table 
     used in the sub-SELECT clause.
 
     5. Select the custid column for the Customer table. A sub-SELECT 
     always displays just one column. Generally, the column is a linking 
     column between the two tables.
 
     6. Using the Where Builder, build the WHERE clause for the 
     sub-SELECT query, WHERE custstate = 'CA'. QBE and the Where Builder 
     automatically use correlation names or aliases to refer to tables. 
     The alias might be the table name, or a shorthand name such as T1. 
     Leave the name that QBE uses.
 
     7. With the Customer query active, from the Query menu, select Copy 
     Query to Clipboard.
 
     8. Return to the QBE window containing the query for the Transmaster 
     table and open the Where Builder.
 
     9. The custid column is used for the sub-SELECT comparison. Select 
     the custid column and add it to the WHERE clause. Add the operator 
     for a sub-SELECT comparison, IN. Next, enter an open parentheses, 
     (.
 
     10. The Where Builder is modal. While you are building your WHERE 
     clause, you do not have access to the menus or other windows. You 
     can't select Copy Query from Clipboard from the Query menu. Instead, 
     use one of the standard Windows paste hot keys, [Shift][Ins] or 
     [Ctrl]V, to copy the sub-SELECT query from the clipboard. Enter a 
     closing parentheses, ).
     
     11. Browse the query in the Data Browser. Only those rows meeting the 
     condition specified by the sub-SELECT comparison, sales data for 
     customers from California, are displayed.
 
     You can toggle between the Data Browser and QBE to further refine 
     your query. The query can be saved as a view, and then retrieved and 
     modified later.
 
     In addition to sub-SELECT queries, the editable WHERE clause in the 
     Where Builder allows you to create other queries using complex SELECT 
     command options, such as GROUP BY and HAVING. To enter a GROUP BY 
     clause, you must first enter an actual WHERE clause, such as colname 
     IS NOT NULL. Then add the GROUP BY clause to the end of the WHERE 
     clause. The Expression Builder, available when selecting columns, 
     allows you to edit expressions, further expanding the type of queries 
     you can build. You can build these complex queries in QBE, save them 
     as views, and retrieve the data into the Data Browser, but might not 
     be able to modify the complex query in QBE.