The ability to join two or more tables (and/or views) easily is a major advantage that distinguishes relational systems from nonrelational systems. The join capability simplifies the task of retrieving data from different tables to build a single result table holding all the necessary data.
You implement this join by forming a query which retrieves data from more than one table. Your SELECT statement includes columns (qualified by table name) from two or more tables. The FROM clause of your query names the tables used as qualifiers in the select-statement.
All the columns specified in the SELECT form the result table. Thus, if you specify the column CUST_NO from the CUSTOMERS table and the column CUST_NO from the ORDERS table, your result table includes two CUST_NO columns, each qualified by the original table name.
You can reference up to 20 tables in a FROM clause when you are performing a join. For example, if a view is based on five tables, you can name that view in the FROM clause, and up to fifteen other tables.
Also see Left Outer Joins.
Example
The following example joins the CUSTOMERS and ORDERS tables. This example is taken from DBCOBSQA, a sample program available on the installation tape.
Note: You can only join tables which have the same security type, that is to say, either the CA Datacom/DB External Security Model or the SQL Security Model. See the CA Datacom Security Reference Guide for more information about Security Models.
Problem
List the customer number, name and order ID for those customers who
have outstanding orders.
Solution
.
.
(COBOL statements)
.
.
1 EXEC SQL
2 DECLARE CUSTORD CURSOR FOR
3 SELECT CUSTOMERS.CUST_NO, ORD_ID, NAME
4 FROM CUSTOMERS, ORDERS
5 WHERE CUSTOMERS.CUST_NO = ORDERS.CUST_NO
6 ORDER BY CUSTOMERS.CUST_NO
7 END-EXEC
.
.
(COBOL statements)
.
.
Line 3
The SELECT statement specifies the columns to be selected from each table. Column names which are the same in each table are qualified by the table name, such as CUSTOMERS.CUST_NO. ORD_ID is not qualified since it exists only in the ORDERS table and NAME is not qualified since it exists only in the CUSTOMERS table.
Line 4
Both tables are named in the FROM clause, indicating that the result table includes the retrieved data from each table.
Line 5
The WHERE clause specifies that the value of the CUST_NO column in each table must be equal to be selected for the result table. If a customer does not have an outstanding order, then the CUST_NO value does not appear in the ORDERS, nor in the result of the join. The comparison is possible since the columns have comparable data types. For comparison rules, see Basic Operations (Assignment and Comparison).
Line 6
The ORDER BY clause specifies that the rows in the result table be in ascending order according to the customer number.
Sample Output
Following is the report produced by running DBCOBSQA.
----------------------------------------------- CURRENT ORDERS ---------------------------------------------PAGE 1 CUSTOMER NO CUSTOMER NAME --------------------------------------------------------------------------------------------------------------------- 0030 CANNON TOOLS CO 0230 CHEMICAL MUTUAL 1210 LINGBERGH INDUSTRIES 1210 LINGBERGH INDUSTRIES 1450 UNION TRANSPORTATION 1630 MARBURY MATERIALS 1850 TECH CASTLE RESEARCH 1890 FIRST STREET BANK CORP 2050 TRANSAMERICAN PUBLISHING END OF REPORT
|
Copyright © 2014 CA.
All rights reserved.
|
|