C Copyright © Oracle Corporation 1995.  All Rights Reserved.
	SUBROUTINE list_record

C---------------------------------------------
C Subroutine to list all the employees and
C the college that they attended.
C---------------------------------------------

	IMPLICIT NONE
	CHARACTER last_name*14,first_name*10,confirm
	CHARACTER degree*3,degree_field*15

&RDB&	DATABASE EXTERNAL pers = FILENAME 'MF_PERSONNEL' 
&RDB&	DBKEY SCOPE IS FINISH

	WRITE (6,90)
90	FORMAT ('1',T25,'**** LIST EMPLOYEES/COLLEGES ****'///)

&RDB& 	START_TRANSACTION READ_ONLY                    

C------------------------------------------------------
C For each EMPLOYEES record that has a corresponding   
C record in DEGREES, print the DEGREES record.
C------------------------------------------------------

&RDB&		FOR E IN EMPLOYEES SORTED BY E.LAST_name
&RDB&		    FOR D IN DEGREES WITH 
&RDB&			D.EMPLOYEE_ID = E.EMPLOYEE_ID
&RDB&			GET
&RDB&				last_name = E.LAST_NAME;
&RDB&				first_name = E.FIRST_NAME;
&RDB&				degree = D.DEGREE;
&RDB&				degree_field = D.DEGREE_FIELD;
&RDB&			END_GET
		    TYPE 100,first_name,last_name,degree,degree_field
100		    FORMAT('/',' Name is: ',A,' ',A,/,'   Degree 
	1is: ',A,'   Degree field is: ',A/)
&RDB&		    END_FOR                                  

C------------------------------------------------------------
C Use the NOT ANY clause to create a stream of all the 
C records in EMPLOYEES that do not
C have an associated record in degrees. Then use the
C FIRST clause to step through this stream.  The FOR
C statement previously created for the EMPLOYEES relation 
C is still active; this will cause the FIRST clause to 
C step through the stream created by the NOT ANY clause.
C------------------------------------------------------------
                                       
&RDB&		    FOR FIRST 1 D IN DEGREES WITH 
&RDB&			NOT ANY D1 IN DEGREES WITH
&RDB&			D1.EMPLOYEE_ID = E.EMPLOYEE_ID
&RDB&			GET
&RDB&				last_name = E.LAST_NAME;
&RDB&				first_name = E.FIRST_NAME;
&RDB&			END_GET                       

C------------------------------------------------------------
C Print the names of the EMPLOYEES who do not have a record
C stored in DEGREES.
C------------------------------------------------------------

		    TYPE 200,first_name,last_name
200		    FORMAT('/',' Name is: ',A,' ',A,/,'   Does not have
	1 this information stored in the database'/)
&RDB&		    END_FOR
&RDB&		END_FOR
&RDB&	COMMIT

	PRINT *, '  '
	TYPE 300
300	FORMAT ('$',' Press RETURN to continue')
	ACCEPT 400, confirm
400	FORMAT (A)

	RETURN
	END