Joining Tables or Database Records › Retrieving Data Rrom Tables and Records

Retrieving Data Rrom Tables and Records

Signing on

To join information from a table and a database record, sign on to the subschema that contains the database record and sign on to the table. Use a view id in the SIGNON statement to keep each signon active and to qualify records and tables that share the same name:

signon table emp view emptab !
signon ss=empss01 view empssc&RB.

Comparing view IDs to alias names

A view ID applies when you sign on to more than one subschema. It qualifies records or tables that have the same name in different subschemas.

An alias applies to records and tables in the SELECT statement. It qualifies fields (columns) and sets that have the same name in different records or tables.

Coding the SELECT statement

Once you have signed on to the subschemas, code the SELECT statement like this:

1. Enter column names and/or record field names following the SELECT keyword: lastname, dept-name-0410.
2. Enter table and/or record names following the FROM keyword. If the record and table share the same name, qualify them with the view id you assigned to the subschema at signon: emptab.emp, empssc.department.
3. Equate values in the WHERE clause. If you are retrieving information from a table and a database record, compare a column to a field: deptid = dept-id-0410

Example— Joining a table and a record

Join table EMP and database record DEPARTMENT to list all employees and their associated departments by:

1. Signing on to the EMPSS01 subschema in dictionary TSTDICT.
2. Signing on to the EMP table in dictionary ASFDICT.
3. Select the employee's name from table EMP and the department's name from record DEPARTMENT where a table row and record occurrence have the same department ID value. The ORDER BY clause instructs CA OLQ to display the rows alphabetically by employees names.

   signon ss empss01 dictname tstdict view empssc !
   signon table emp dictname asfdict view emptab !
   select lastname as employee, dept-name-0410 as department
     from emp, department
     where deptid = dept-id-0410
   

     order by lastname ! display
   

                              EMP/DEPARTMENT REPORT
                                    mm/dd/yy

          EMPLOYEE                           DEPARTMENT
       ---------------      ---------------------------------------------
       ANGELO               PUBLIC RELATIONS
       BANK                 PUBLIC RELATIONS
       DOUGH                INTERNAL SOFTWARE
       GALLWAY              INTERNAL SOFTWARE
       GARFIELD             INTERNAL SOFTWARE
       GRANGER              INTERNAL SOFTWARE
       HEAROWITZ            INTERNAL SOFTWARE
       HENDON               EXECUTIVE ADMINISTRATION
       JACKSON              PUBLIC RELATIONS
       JACOBI               INTERNAL SOFTWARE
       JENSEN               INTERNAL SOFTWARE
       LITERATA             INTERNAL SOFTWARE
       MCDOUGALL            PUBLIC RELATIONS
       PAPAZEUS             EXECUTIVE ADMINISTRATION