The SQL Procedure

Example 10: Querying an In-Line View

Procedure features:

FROM clause

in-line view

Tables:

PROCLIB.STAFF2, PROCLIB.SCHEDULE2, PROCLIB.SUPERV2

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';

options nodate pageno=1 linesize=80 pagesize=60;

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;

Output: Listing

                        All Flights for Each Supervisor                        1

                                                                      Job
  Surname          Emp_ID  Hometown         FlightNumber  FlightDate  Category
  ----------------------------------------------------------------------------
  MARSHBURN        1106    STAMFORD         579              05MAR94  PT      
  DENNIS           1118    NEW YORK         132              01MAR94  PT      
  DENNIS           1118    NEW YORK         271              04MAR94  PT      
  KIMANI           1126    NEW YORK         579              05MAR94  TA      
  TUCKER           1882    NEW YORK         622              03MAR94  ME

Top of Page