×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to create a VIEW from metadata definition?

How to create a VIEW from metadata definition?

How to create a VIEW from metadata definition?

(OP)
Say I have these four tables:

Product
    productKey
    productTypeKey
    quantityOnHand

ProductType
    productTypeKey
    name

ProductAttribute
    productAttributeKey
    name
    valueType    -- 1=boolean, 2= float, etc.
    productTypeKey

ProductAttributeValue
    valueBoolean NUMBER(1)
    valueFloat FLOAT
    valueInteger NUMBER
    valueString VARCHAR2(4000)
    productAttributeKey
    productKey

A Product represents a quantity of identical items of a specific ProductType (navy men's slacks, 32" waist, 34" inseam uncuffed, for example). For each ProductType, the ProductAttribute table defines a set of attributes supplied at runtime by users of the application. For example, they might create a ProductType named 'PantsCo Slacks' that has ProductAttributes named 'inseam' (float), waist (float), 'color' (string) and 'cuffed' (boolean). The values of those attributes for a particular Product (32.5, 34, 'navy', 0) are then stored in the ProductAttributeValue table, one row for each value.

(By the way, this is a toy example--the real problem could have dozens of attributes for each product type, and thousands of product types, hence the need for this kind of approach.)

Managing this indirection is straightforward in a programming language, but I also need to make things easy for people using SQL directly. So here's my question (finally!):  is it possible to create a VIEW (using dynamic SQL) with column names I've pulled out of the ProductAttribute table, so the user would see a view with columns inseam, waist, color, cuffed, for example?

I'm guessing the answer is no, since I'll need to collect values from multiple rows of the ProductAttributeValue table for each row of the view. Would a stored procedure allow me to create a result set whose column names and data types are only determined at execution time? I'm currently using Oracle, if that matters.

Does anyone know of other RDMBS solutions to using metadata like this in the literature? Any help, ideas, or simpler approaches would be greatly appreciated.

Thanks!
Bob

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close