obj or vew to flatten "cell" records into a "row" record

I have ProdPrice records which are essentially "cells" in a price matrix.

create table ProdPrice {

   ProdNum number (12,0),
   Quality char(1),
   Condition char(1),
   Price number(15,3),
   ... (other stuff)
}

with primary key (ProdNum, Quality, Condition)

where Quality is in [ 0, 1, 2, 3 ] and Condition in [ 0, 1, 2 ]

I want to create a view or object view representing one "row" in the matrix with that "row" containing all prices with the same Quality. Essentially I want to "flatten" the cells into Quality "rows" for viewing and editing.

An example Quality "row" type might look like:

create type ProdPriceQualityRow {

   ProdNum number (12,0),
   Quality char(1),

   PriceCondition0 number(15,3),
   PriceCondition1 number(15,3),
   PriceCondition2 number(15,3),
   PriceCondition3 number(15,3)

I *think* I now need to

create view ProdPriceQualityRowView of type ProdPriceQualityRow

   with object identifier (ProdNum, Quality) as ....

but I'm not sure how to complete the view / object and its associated instead of trigger.

Does anyone have any examples of how to create views / objects / triggers that work like this?

I've read most of the Oracle Application Developers Guide - Object Relational Features but can not seem to warp their examples into the above.

Thanks
R.Parr
Temporal Arts Received on Fri Jul 19 2002 - 15:48:34 CDT