PL/SQL, cursors, union, master detail

From: Ted Zuschlag <zuschlat_at_osshe.edu>
Date: 1995/12/26
Message-ID: <4bptm0$fp1_at_osshe.edu>#1/1


I saw a nice example of using SQL to produce a 'master-detail' report. The technique involves using the UNION operator to merge the master table and the detail table, and fabricating a sort key to order the merged/unionized rows. The results look something like:

	0	10	Department: Accounting
	1	10	
	2	10	CLARK
	2	10	KING
	2	10	MILLER
	3	10
	0	20	Department: Research
	1	20
	2	20	ADAMS
	2	20	FORD
	2	20	JONES
	2	20	SCOTT

  So column 1 is the extrinsic sort key, and column 2 and 3 are the intrinsic ones.
  I am looking for clues about how to make this technique work for batch processing. Help me guess-- could a COBOL program with embedded SQL process the union of two tables? My concern is whether it is a problem to have "multiple record types" (i.e. different kinds of row data) coming from a single cursor. I mean let 'HD' denote a header record, and 'DE' denote a detail record (which has a different structure than the header, of course.)
  Under Oracle 7, can my batch processing cursor present me with HD, DE, DE, DE, DE, HD, DE, DE, HD, DE, HD, DE, DE, DE, etc. ?   

  I haven't tried anything, just yet. I don't want to re-discover known limitations.

--
Ted Zuschlag		zuschlat_at_osshe.edu		
Corvallis, Oregon	USA
h: (541) 753-6354	w: (541) 737-6219
Received on Tue Dec 26 1995 - 00:00:00 CET

Original text of this message