SQL Procedure

Example 10: Querying an In-Line View

Features:

FROM clause : in-line view

Table names:

This example shows an alternative way to construct the query that is explained in Joining Three Tables by joining one of the tables with the results of an in-line view. The example also shows how to rename columns with an in-line view.

Program

libname proclib 'SAS-library';

proc sql;
   title 'All Flights for Each Supervisor';
   select three.*, v.jobcat

from (select lname, s.idnum, city, flight, date
               from proclib.schedule2 s, proclib.staff2 t
               where s.idnum=t.idnum)

as three (Surname, Emp_ID, Hometown,
                           FlightNumber, FlightDate),

proclib.superv2 v
      where three.Emp_ID=v.supid;

Program Description

Declare the PROCLIB library.The PROCLIB library is used in these examples to store created tables.

libname proclib 'SAS-library';

Select the columns.The SELECT clause selects all columns that are returned by the in-line view (which will have the alias Three assigned to it), plus one column from the third table (which will have the alias V assigned to it).

proc sql;
   title 'All Flights for Each Supervisor';
   select three.*, v.jobcat

Specify the in-line query.Instead of including the name of a table or view, the FROM clause includes a query that joins two of the three tables. In the in-line query, the SELECT clause lists the columns to select. IdNum is prefixed with a table alias because it appears in both tables. The FROM clause lists the two tables for the join and assigns an alias to each table. The WHERE clause specifies the columns that join the tables. The STAFF2 and SCHEDULE2 tables have an IdNum column, which has related values in both tables.

from (select lname, s.idnum, city, flight, date
               from proclib.schedule2 s, proclib.staff2 t
               where s.idnum=t.idnum)

Specify an alias for the query and names for the columns.The alias Three refers to the results of the in-line view. The names in parentheses become the names for the columns in the view.

as three (Surname, Emp_ID, Hometown,
                           FlightNumber, FlightDate),

Join the results of the in-line view with the third table.The WHERE clause specifies the columns that join the table with the in-line view. Note that the WHERE clause specifies the renamed Emp_ID column from the in-line view.

proclib.superv2 v
      where three.Emp_ID=v.supid;

Output

Query of an In-Line View