Help with database query tool

Here's the problem I'm trying to solve:

	I have an Oracle database with a few hundred tables,
	within which there are several dozen logical groupings of
	tables we call "subjects".  Subjects are defined in a
	"join" table.  The purpose of the subjects and the join table
	is to provide the information needed by an adhoc query tool
	to automatically formulate the SQL necessary which will
	connect the tables and produce a report.

	Each subject has a "core" table, which contains the basic data,
	and a mess of "related" tables.  The related tables contain
	expansions on the data in the core table.  The relationship
	between the core and related tables is described in the join
	table, it names the columns which when connected form the
	join.  For example, the core table might contain SSN, one of
	the related tables would contain SSN, NAME, ADDRESS, and so on.
	SSN is named in the join table as the column upon which
	the tables must be connected to form the relationship.
	So far, duck soup.

	I present the users with a menu of all of the columns in the
	subject (e.g. all of the columns in the core table and all of
	its related tables), they select a set of columns they would
	like to see on a report and I generate/execute the SQL, format
	and print the report.

Where I am:

	I have a prototype, written in Visual Basic, that works, but
	it is an enormous amount of code and I would like to throw it
	away and replace it with off-the-shelf report writer or perhaps
	an OLE automation object for use in Excel.

	The kicker is this: As far as I know, Excel doesn't have the
	"subject" mechanism I defined to encapsulate the "implicit
	relationship" amongst a set of tables.  Nor do I know of any
	other adhoc report query program that does.  This mechanism is
	very important to my client population which does not want to
	learn all about relational databases, tables, and queries.

	One approach would be to discard the "subjects" concept
	altogether and replace it with Oracle views; each view would
	essentially define the same links as the subject definition
	in the join table.  Then Excel would be able to access the
	views.  The problem with this is that the SQL statement is
	woefully non-optimal.  Using a view might require that several
	dozen joins be made when the user was actually only looking
	for some columns out of a couple of tables in the subject.

Where I am headed:

	I am looking at several solutions.  The obvious one would be
	for someone to come along and say "Hey, you're going about it
	all wrong, here's the solution in a nutshell" and I could go
	away happy.  Another would be to retain the subject-awareness
	part of the prototype and cause it to hand off either optimal
	SQL, or the actual query results, to Excel, by way of DDE or
	OLE.  Another one would be an off-the-shelf report writer
	that "does everything but eat".

So, any of you folks know:

	If there are any adhoc query tools that provide this kind of
	capability?  If I can DDE the SQL into Excel?  If I need to
	do the query in an OLE object and hand the results into Excel?
	I barking way out on the wrong limb?

	Any help or suggestions would be greatly appreciated.  Please
	email responses 'cause I don't get around these here parts
	very often (pardner).  If there are some "me too's" I'll post
	a synopsis.

	Thanks in advance.

	Bruce W. Robinson
	(303) 538-4935

	66


	 >

	 ^ ....................  brucer_at_dr.att.com