Re: Normalization and Derived Information
From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Fri, 24 Sep 2004 08:01:03 -0400
Message-ID: <0a21jc.9df.ln_at_mercury.downsfam.net>
>
> Oh wow, a view generator! I like that!
>
> On my very first Oracle project, (after about 10 years of Rdb) I came up
> with a sort of semiautomatic view generator. You gave it some parameters
> that roughly said what you wanted, and a "pattern" for what the view
> might
> look like, and it generated a "first approximation".
>
> The first approximation often needed alteration before it was even usable,
> let alone correct. But the automatic part took care of almost all the
> scut
> work for me. And that's where the errors creep in anyway.
>
> I love the idea of a view generator, and I suspect that the concept can
> be carried much farther than I did.
Date: Fri, 24 Sep 2004 08:01:03 -0400
Message-ID: <0a21jc.9df.ln_at_mercury.downsfam.net>
Laconic2 wrote:
>
> "Kenneth Downs"
>
>> So if its not too late to make a long story short, I coded up a view >> generator that would take formulas and foreign key definitions from the >> DD
>
> Oh wow, a view generator! I like that!
>
> On my very first Oracle project, (after about 10 years of Rdb) I came up
> with a sort of semiautomatic view generator. You gave it some parameters
> that roughly said what you wanted, and a "pattern" for what the view
> might
> look like, and it generated a "first approximation".
>
> The first approximation often needed alteration before it was even usable,
> let alone correct. But the automatic part took care of almost all the
> scut
> work for me. And that's where the errors creep in anyway.
>
> I love the idea of a view generator, and I suspect that the concept can
> be carried much farther than I did.
Since a view generator is a special case of a SELECT generator, my thinking runs towards using materialized derived columns in tables, so that the selects are always pulling simple columns and never calculating on the fly.
With this, you could specify a select in comma-delimited data as:
ReportName,Usenet Example
table1,col1 table1,col3 table1,col7 table1,col8 table2,colA table2,colB table2,colF
From which it is trivial to look up the JOIN between table1 and table2 and produce:
SELECT a.col1,a.col3,a.col7,a.col8,
b.colA,b.colB,b.colF
FROM table1 a JOIN table2 b on a.key = b.key
After that the order by and group by are pretty much handled the same way. Filters require you to either allow parsable expressions or go to assembly style operator/operand lists.
-- Kenneth Downs Use first initial plus last name at last name plus literal "fam.net" to email meReceived on Fri Sep 24 2004 - 14:01:03 CEST