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

C--------------------------------------------------------
C This subroutine demonstrates the use of the declared
C START_STREAM statement.  The output of this
C program is merely a random matching of each CANDIDATES
C record with an EMPLOYEES record.  
C---------------------------------------------------------

	IMPLICIT NONE
	LOGICAL emps_end,cands_end
	CHARACTER employee_id*5,last_name*14,first_name*10,confirm
	CHARACTER cand_last_name*14,cand_first_name*10,cand_status*257

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

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


C-------------------------------------------------------
C Declare the streams that will be used to process the 
C EMPLOYEES and CANDIDATES records.
C-------------------------------------------------------

&RDB&	DECLARE_STREAM cands USING ca IN candidates 
&RDB&		SORTED BY ca.last_name
&RDB&	DECLARE_STREAM emps USING em IN employees
&RDB&		SORTED BY em.first_name

&RDB&	START_TRANSACTION READ_ONLY

C------------------------------------------------------
C Open both streams and set a flag for the 
C end-of-stream condition to false.
C------------------------------------------------------

&RDB&	    START_STREAM cands
&RDB&	    START_STREAM emps 

	    	emps_end = .FALSE.
		cands_end = .FALSE.

C-------------------------------------------------------
C Fetch a record from the CANDIDATES relation.  If there
C are no records to retrieve, set the end-of-stream flag
C to true.  Otherwise, retrieve the record.
C-------------------------------------------------------		

&RDB&		FETCH cands
&RDB&			AT END
				cands_end = .TRUE.
&RDB&		END_FETCH
		IF (.NOT.(cands_end)) THEN
&RDB&			GET
&RDB&				cand_last_name = ca.last_name;
&RDB&				cand_first_name = ca.first_name;
&RDB&				cand_status = ca.candidate_status;
&RDB&			END_GET
		END IF

C--------------------------------------------------------
C Fetch a record from the EMPLOYEES relation.  If there
C are no records to retrieve, set the end-of-stream flag
C to true.  Otherwise, retrieve the record.
C--------------------------------------------------------

&RDB&		FETCH emps
&RDB&			AT END
				emps_end = .TRUE.
&RDB&		END_FETCH
		IF (.NOT.(emps_end)) THEN
&RDB&			GET
&RDB&				last_name = EM.LAST_NAME;
&RDB&				first_name = EM.FIRST_NAME;
&RDB&				employee_id = EM.EMPLOYEE_ID;
&RDB&			END_GET
		END IF

C-------------------------------------------------------
C If a record exists in both relations then print a 
C report.
C-------------------------------------------------------

		PRINT *,' Employees:                 Candidates:'
		PRINT *,'-------------------------  -------------------------'
		DO WHILE (.NOT.(cands_end))
			TYPE 100,last_name,first_name,
	1                        cand_last_name,cand_first_name
100			FORMAT('/','  ',A,' ',A,T31,A,' ',A)
&RDB&			FETCH cands
&RDB&				AT END
					cands_end = .TRUE.
&RDB&			END_FETCH
			IF (.NOT.(cands_end)) THEN
&RDB&				GET
&RDB&					cand_last_name = CA.LAST_NAME;
&RDB&					cand_first_name = CA.FIRST_NAME;
&RDB&					cand_status = CA.CANDIDATE_STATUS;
&RDB&				END_GET
			END IF
			IF (.NOT.(emps_end)) THEN
&RDB&				FETCH emps
&RDB&					AT END
						emps_end = .TRUE.
&RDB&				END_FETCH
				IF (.NOT.(emps_end)) THEN
&RDB&				    GET
&RDB&					last_name = EM.LAST_NAME;
&RDB&					first_name = EM.FIRST_NAME;
&RDB&					employee_id = EM.EMPLOYEE_ID;
&RDB&				    END_GET
				END IF
			END IF
		END DO

C----------------------------------------------
C Close both streams.
C----------------------------------------------

&RDB&		END_STREAM emps
&RDB&		END_STREAM cands

&RDB&	COMMIT

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

	RETURN
	END