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
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. ?
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-6219Received on Tue Dec 26 1995 - 00:00:00 CET