Legacy Systems Reporting

From: Programming <sysdev_at_mb.sympatico.ca>
Date: 1997/03/26
Message-ID: <3339562A.8F7@mb.sympatico.ca>#1/1

Legacy systems reporting.
Moving large numbers of rows of data,
into one big concatenated field.

We are currently in the process of moving our old Cobol system to an Oracle system. Some of the reporting is presenting
a special challenge.

The conceptual schema,
in both the old and the new system is:
Any one client can have many licenses

The new table structure has been designed to reflect this and is conceptually correct.

1		Many 
Client	->	Licenses
Fields are: 
Key		       Foreign key + license field

A report from the old Cobol system, listed the client, followed by all the licenses concatenated onto one line.

Client_no: license1, license2, license3, license4, license5, license6, etc.

The user insists that the new system must report in this same old format. So the question is, how can we complete this kind of report efficiently?

Normally, a query on the licenses for any client, would list them over as many ROWS as there are licenses. We want all the licenses on one line.

Dragging the information into our front-end software has been very inefficient. How can we do this all on the Oracle or Unix server?

We have considered:
- 3GL programs with embedded sql and cursors

• stored procedures on Oracle.
• PL/SQL

The process conceptually would be:
- read the record

• append to a memory variable.
• output, or store output somewhere.

Does anyone else have any experience with this kind of legacy transformation? Is there any idea that we have not considered? Are there any better ideas? Received on Wed Mar 26 1997 - 00:00:00 CST