nested tables and ANSI JOINs

From: Kurt Laugesen <kula_at_int.tele.dk>
Date: 9 May 2002 09:55:19 -0700
Message-ID: <7c804feb.0205090855.67fcb479@posting.google.com>

I have created a small testcase:

create table account
(id number,
fk number)
/

create type address_general_t as object

(firstname     varchar2(40)
,lastname      varchar2(40)

)
/

create type address_tab_t as table of address_general_t
/

create table person (
 id number
,address address_tab_t)
tablespace stab nested table address store as address_tab;

Now if I create a view like this it works fine: create or replace view v_person as
  select ac.id,

         ad.firstname from
  account ac left outer join person p on  (ac.fk=p.id),
 table(p.address) ad;

but if I remove the 'left outer'
I will get an error
ORA-22904: invalid reference to a nested table column

If I revert to the old syntax (also without an outer join) create or replace view v_person as
  select ac.id,

         ad.firstname from
  account ac ,
  person p,
  table(p.address) ad
 where ac.fk=p.id;

everything works fine!

can anyone explain this behaviour?

TIA
Kurt Laugesen Received on Thu May 09 2002 - 11:55:19 CDT