|how to use custom datatype in SQL ??? [message #329802]
||Thu, 26 June 2008 10:40
Registered: June 2008
I suppose my question is pretty common but didn't find answer . Any hints would be highly appreciated ! |
The Oracle 9i has one exemplary table
CUSTOMER_ID .... NUMBER(6) not null,
CUST_FIRST_NAME ..... VARCHAR2(20),
CUST_LAST_NAME ..... VARCHAR2(20),
CUST_ADDRESS ..... CUST_ADDRESS_TYP
CUST_ADDRESS is CUST_ADDRESS_TYPE, and it comes from type
CREATE OR REPLACE TYPE cust_address_typ
( street_address ..... VARCHAR2(40)
, postal_code ..... VARCHAR2(10)
, city ...... VARCHAR2(30)
, state_province ...... VARCHAR2(10)
, country_id ...... CHAR(2) );
SELECT CSUTOMER_ID, CUST_ADDRESS FROM CUSTOMERS
CUSTOMER_ID, CUST_ADDRESS .STREE_ ADDRESS,CUST_ADDRESS .POSTAL_CODE,CUST_ADDRESS .CITY,CUST_ADDRESS .STATE_PROVINCE, CUST_ADDRESS .COUNTRY_ID.
What should SQL be in order to show Customer ID and CUST_ADDRESS .STREE_ ADDRESS only ?
SELECT CUSTOMER_ID, CUST_ADDRESS .STREE_ ADDRESS doesn't work.
Can I use CUST_ADDRESS as indepentent table ? How do I open it ( such as Select .. ) in SQL ?
If I can not use CUST_ADDRESS as independent table, how do I create table such as CUST_address2 and feed all data from CUST_ADDRESS ?
Hope my question is clear. Thank you very much.