Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How create a view of 2 object-tables(One has REF attribute,the other has not)
A copy of this was sent to a0471521_at_student.chula.ac.th
(if that email address didn't require changing)
On Tue, 17 Aug 1999 08:00:30 GMT, you wrote:
>Hi,
> I want to create a view connecting 2 object tables. Two tables like
>this.
> - Customers table is of t_customer *******
> - desc t_customer (object)
> id varchar2(5),name varchar2(10),address varchar2(30),
> tariff REF of t_tariff
> - desc t_tariff (object)
> tariff_cde varchar2(5),tariff_desc varchar2(20)
> - Customer2 table is of t_customer2 *******
> - desc t_customer2 (object)
> id varchar2(5),name varchar2(10),address varchar2(30)
>
> and then I want to create a view over Customers and Customer2 like
>this.
> Create view customer_view of t_customer as
> select a.id,a.name,a.address,a.tariff from customers a
> union (select b.id,b.name,b.address,null
> from customer2 b)
> But !! it's error because Customer2 table has different number of
>attributes from Customers which in Customers has REF attribute but in
>Customer2 has not. How can I create a view like this?
>Thanks,
>Pukky
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
You need to select a REF in the second query. One method that might work for you would be to create an EMPTY_TABLE of an object type and outer join to it -- for example:
SQL> create type a_type as object ( x int );
2 /
Type created.
SQL> create table a_table of a_type;
Table created.
SQL> create table EMPTY_TABLE of a_type; Table created.
SQL> create type b_type as object( x int, y ref a_type );
2 /
Type created.
SQL> create table b_table of b_type;
Table created.
SQL> create or replace view a_view
2 as
3 select b.x, b.y from b_table b
4 union all
5 select a.x, ref(e) from a_table a, empty_table e
6 where a.x = e.x(+)
7 /
View created.
SQL> insert into a_table values ( a_type(1) ); SQL> insert into a_table values ( a_type(2) ); SQL> insert into a_table values ( a_type(3) ); SQL> insert into b_table select a.x, ref(a) from a_table a;
SQL> select * from a_view;
X Y
---------- ---------------------------------------- 1 00002202085247C599A66405F3E03408002079DD 715247C599A65D05F3E03408002079DD71 2 00002202085247C599A66505F3E03408002079DD 715247C599A65D05F3E03408002079DD71 3 00002202085247C599A66605F3E03408002079DD 715247C599A65D05F3E03408002079DD71 1 000028020901055D5401055D5400000000000000 005247C599A65E05F3E03408002079DD7101055D 4C0105 2 000028020901055D5401055D5400000000000000 005247C599A65E05F3E03408002079DD7101055D 4C0105 3 000028020901055D5401055D5400000000000000 005247C599A65E05F3E03408002079DD7101055D 4C0105
6 rows selected.
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Aug 17 1999 - 07:41:13 CDT