| Object View Statement [message #371649] |
Mon, 20 November 2000 09:12  |
Roman Langfeld
Messages: 2
Registered: November 2000
|
Junior Member |
|
|
Hi everybody!
i'm trying to create an object view on two tables
(PurchaseOrder and OrderDetails), where one record
in the PurchaseOrder table corresponds to one or
more rows in the OrderDetails table.
By using an object view i want to retrieve the data
in a master-details manner.
Can anyone show me how to create such an object view?
thanks in advance!
|
|
|
|
| Re: Object View Statement [message #371652 is a reply to message #371649] |
Mon, 20 November 2000 09:41  |
Babu Paul
Messages: 38
Registered: November 2000
|
Member |
|
|
Hi,
Try creating a view joining these two tables on the key field.
For ex:
CREATE VIEW view_name AS
SELECT a.order_no, b.details,
FROM purchaseorder a
,orderdetails b
WHERE a.order_no = b.order_no ;
Hope this helps
Good Luck!
Babu
|
|
|
|
| Re: Object View Statement [message #371654 is a reply to message #371652] |
Mon, 20 November 2000 11:02 |
Roman Langfeld
Messages: 2
Registered: November 2000
|
Junior Member |
|
|
Babu, thanks for your quick response!
The problem, though, is that the output of a query for that view looks like:
order_no | details
------------------
1 | item1
1 | item2
2 | item1
...and so on.
But I try to work on the data as if I would use a nested details table.
In order to use the existing relational tables I thought I could use an
object view which would work like a 'filter'.
The result of a query for that object view should look like:
order_no | details
------------------
1 | item1
| item2
| item3
------------------
2 | item1
| item2
| item3
This is the way I tried to create an object view:
First I created an object that describes the details:
CREATE TYPE details_obj AS OBJECT (item_no, item_name, price);
... then I created an object that describes a purchase order with
a column named 'details' of type details_obj (which i just created):
CREATE TYPE po_obj AS OBJECT (order_no, customer, details);
And then I defined an object view, i.e. a view based on the purchase
order object:
CREATE VIEW po_view OF po_obj WITH OBJECT IDENTIFIER (order_no) AS
SELECT a.order_no, a.customer, b.item_no, b.item_name, b.price
FROM purchaseorder a, orderdetails b
WHERE a.order_no = b.order_no;
But this apparently doesn't work because the po_obj consists of three
attributes where as in the view declaration I select five attributes.
And that's where I got stuck. Maybe I'm all wrong and there is no way
to do it like that. If this is the answer to my question, it's ok. But
I need to be sure.
|
|
|
|
| Re: Object View Statement [message #371666 is a reply to message #371652] |
Tue, 21 November 2000 05:31 |
Babu Paul
Messages: 38
Registered: November 2000
|
Member |
|
|
Roman,
You are right, you cannot select five attributes when the object type itself holding only 3 attributes. To work around this problem, I suggest you better create another object type which contains both details_obj and po_obj as items.
Ex. create type third_obj(a details_obj,b po_obj);
now try to create an object view on this object type
ex: create or replace view po_view of third_obj
with object identifier....
hopefully this should work.
Good Luck!
Babu
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
