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

Home -> Community -> Usenet -> c.d.o.misc -> Re: How create a view of 2 object-tables(One has REF attribute,the other has not)

Re: How create a view of 2 object-tables(One has REF attribute,the other has not)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 17 Aug 1999 12:41:13 GMT
Message-ID: <37c0581d.92568175@newshost.us.oracle.com>


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

Original text of this message

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