I have a customer table whose CUST_ADDRESS column is a TYPE as OBJECT
Customer Table DDL
CUST_ADDRESS_TYP structure
I want the value of city from the CUST_ADDRESS_TYP which is subset of CUST_ADDRESS column in my CUSTOMER table.
please help me to write the query for above result.
Not able to write the correct query
New contributor
Aashish Patel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
You can use table_alias.column_name.object_attribute to get the value (the table_name or table_alias is required before the column_name):
SELECT id,
name,
c.address.street,
c.address.city
FROM customer c
Which, for the sample data:
CREATE TYPE address_type IS OBJECT(
street VARCHAR2(20),
city VARCHAR2(20)
);
CREATE TABLE customer (
id NUMBER,
name VARCHAR2(20),
address address_type
);
INSERT INTO customer (id, name, address)
VALUES (1, 'Alice', address_type('Somewhere Street', 'Random City'));
Outputs:
| ID | NAME | ADDRESS.STREET | ADDRESS.CITY |
|---|---|---|---|
| 1 | Alice | Somewhere Street | Random City |
fiddle