I have a Oracle object called Menu
which has a object type as an attribute.
menu type
Create Type Menu as Object(
beer REF beertype,
price float
)
beertype
Create Type beertype as object(
name char(20),
seller char(20)
)
table created using menu type
Create table Sells of Menu
table created using beertype
Create table beer of beertype
Now what I want to do is I want to write a select query which retrieves the name of the beer which has price more than 3.5.
I tried
SELECT beer.name
FROM Sells;
and
SELECT Sells.beer.name
FROM Sells;
Here's an example: test case first:
SQL> create type beertype as object(
2 name char(20),
3 seller char(20)
4 );
5 /
Type created.
SQL> create type menu as object(
2 beer ref beertype,
3 price float
4 );
5 /
Type created.
SQL> create table sells of menu;
Table created.
SQL> create table beer of beertype;
Table created.
SQL> insert into beer (name, seller) values ('Heineken', 'KTC');
1 row created.
SQL> insert into beer (name, seller) values ('Tuborg', 'Plodine');
1 row created.
SQL> insert into sells (beer, price) values ((select ref(b) from beer b where name = 'Heineken'), 10);
1 row created.
SQL> insert into sells (beer, price) values ((select ref(b) from beer b where name = 'Tuborg'), 2.5);
1 row created.
Query which selects beer name whose price is larger than 3.5:
SQL> select b.name, s.price
2 from sells s join beer b on value(b) = deref(s.beer)
3 where s.price > 3.5;
NAME PRICE
-------------------- ----------
Heineken 10
SQL>
Here's a nice article, if you want to read more: Using REF Values for Retrieval of Data Values