table with nested records question

From: Kevin W. <kcw3388_at_hotmail.com>
Date: 11 Jun 2002 15:27:33 -0700
Message-ID: <69087156.0206111427.6011f29a@posting.google.com>

Hi,

I got a table with nested records as listed below.

create type test_typ_nested_del as object (

   nr1_num1   number(4),
   nr1_str    varchar2(35),
   nr1_value  number(1))

/

Create TYPE test_typ_nested_del1 as OBJECT (

   NR2_num number(4),
   NR2_rec test_typ_nested_del)
/

Create table test_tbl_nested_del (

   D1 number,
   D2 test_typ_nested_del1,
   D3 number)
/

Insert into test_tbl_nested_del

   (d1,d2,d3) values (1,
    test_typ_nested_del1(1,
    test_typ_nested_del(1,'A',1)),1);
commit;

SQL> select q.d2.nr2_rec.NR1_NUM1 from test_tbl_nested_del q;

D2.NR2_REC.NR1_NUM1

---

                  1

SQL> select test_tbl_nested_del.d2.nr2_rec.NR1_NUM1 from test_tbl_nested_del; select test_tbl_nested_del.d2.nr2_rec.NR1_NUM1 from test_tbl_nested_del

                              *

ERROR at line 1:
ORA-00904: invalid column name

I am trying to select a column from a nested records withing a table but it won't if I use "test_tbl_nested_del.d2.nr2_rec.NR1_NUM1".

But, interestingly, it works if I use alias for the table name. "q.d2.nr2_rec.NR1_NUM1 from test_tbl_nested_del q;"

It's weird!
Can someone tell me why?

Thanks!

Kevin Received on Tue Jun 11 2002 - 17:27:33 CDT