Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle8 objects error - ORA-00904: invalid column nam
Sorry for multiple postings. I have a conflict btw mailserver and Navigator.
Hi,
No. It should not be. Simply a table alias is required. Two examples are as follows:
SQL> CREATE TYPE address_type AS OBJECT
2 (street VARCHAR2(50),
3 city VARCHAR2(25),
4 state CHAR(2),
5 zip NUMBER)
6 /
Type created.
SQL> CREATE TYPE person_type AS OBJECT
2 (name VARCHAR2(25),
3 address address_type)
4 /
Type created.
SQL> CREATE TYPE customer_type AS OBJECT
2 (customer_id NUMBER,
3 person person_type)
4 /
Type created.
SQL> CREATE TABLE customers of customer_type 2 /
Table created.
SQL> CREATE TABLE customer
2 (customer_id NUMBER,
3 person person_type)
4 /
Table created.
SQL> INSERT INTO customer
2 VALUES(99, person_type('HAKAN', address_type('N/A', 'N/A', 'NA', 99)))
3 /
1 row created.
SQL> INSERT INTO customers
2 VALUES(customer_type(99, person_type('HAKAN', address_type('N/A', 'N/A',
'NA', 99))))
3 /
1 row created.
SQL> SELECT * FROM customer
2 /
CUSTOMER_ID
99
PERSON_TYPE('HAKAN', ADDRESS_TYPE('N/A', 'N/A', 'NA', 99))
SQL> SELECT * FROM customers
2 /
CUSTOMER_ID
99
PERSON_TYPE('HAKAN', ADDRESS_TYPE('N/A', 'N/A', 'NA', 99))
SQL> SELECT c.person.name FROM customer c
2 /
PERSON.NAME
PERSON.NAME
> Rajneesh Kwatra wrote : Should'nt that be 'PERSON_TY.NAME' !!
>
> bijukjoseph_at_hotmail.com wrote:
>
> > Dear all,
> >
> > I have created a customer table as shown below.
> >
> > SQL> desc customer
> > Name Null? Type
> > ------------------------------- -------- ----
> > CUSTOMER_ID NUMBER
> > PERSON PERSON_TY
> >
> > SQL> desc person_ty
> > Name Null? Type
> > ------------------------------- -------- ----
> > NAME VARCHAR2(25)
> > ADDRESS ADDRESS_TY
> >
> > SQL> desc address_ty
> > Name Null? Type
> > ------------------------------- -------- ----
> > STREET VARCHAR2(50)
> > CITY VARCHAR2(25)
> > STATE CHAR(2)
> > ZIP NUMBER
> >
> > SQL> select * from customer
> > 2 /
> >
> > CUSTOMER_ID ----------- PERSON(NAME, ADDRESS(STREET, CITY, STATE, ZIP))
> > -----------------------------------------------------------------------------
> > --- 1 PERSON_TY('Biju K. Joseph', ADDRESS_TY('Street A', 'City Blue', 'CA',
> > 23423))
> >
> > 2
> > PERSON_TY('Shafi Ulla', ADDRESS_TY('Street B', 'City Red', 'AR', 234234))
> >
> > SQL>
> >
> > But when I do a select as shown below it gives an error
> >
> > SQL> select person.name from customer
> > 2 /
> > select person.name from customer
> > *
> > ERROR at line 1:
> > ORA-00904: invalid column name
> >
> > This should work as per oracle manual !
> >
> > Where am I going wrong ?
> >
> > Thanks in Advance
> >
> > Biju Joseph
> >
> > -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> > http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>
> --
> Rajneesh Kwatra
> E-Mail : rk88_at_pantheon.yale.edu
> Phone : (203)432-1898
>
> ------------------------------------------------------------------------
>
> Rajneesh Kwatra <rk88_at_pantheon.yale.edu>
> Consultant
> .
>
> Rajneesh Kwatra
> Consultant <rk88_at_pantheon.yale.edu>
> . HTML Mail
> Work: (203)432-1898
> Netscape Conference Address
> Netscape Conference DLS Server
> Additional Information:
> Last Name Kwatra
> First Name Rajneesh
> Version 2.1
Received on Thu Aug 27 1998 - 13:50:12 CDT