Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> obj or vew to flatten "cell" records into a "row" record

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

From: Randall J. Parr <RParr_at_TemporalArts.com>
Date: Fri, 19 Jul 2002 13:48:34 -0700
Message-ID: <3D387B22.2030202@TemporalArts.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US