CODEWITHSUNDEEP
oracle-databaseoracle11gnested-table
GoobyPrs
GoobyPrs

Reputation: 615

Oracle sql select data from table where attribute is nested table

I have a objects:

create type t_history_rec is object
(
  date_from date,
  current float
);

create type t_history is table of t_history_rec;

and table defined:

create table person
(
  id integer primary key,
  name varchar2(30),
  history t_history

);

and I want to get select name, history.date_from, history.current like this:

name1 date1 current1
name1 date2 current2
name2 date3 current3
...

How to do this?

Upvotes: 1

Views: 678

Answers (2)

Kacper
Kacper

Reputation: 4818

You have some errors. current is reserved

create or replace type t_history_rec is object
(
  date_from date,
  curr float
);
/
create type t_history is table of t_history_rec;
/

Table definition needs store as

create table person
(
  id integer primary key,
  name varchar2(30),
  history t_history
) NESTED TABLE history STORE AS col1_tab;

insert into person (id, name, history) values (1, 'aa', t_history(t_history_rec(sysdate, 1)));
insert into person (id, name, history) values (2, 'aa', t_history(t_history_rec(sysdate, 1), t_history_rec(sysdate, 1)));

Then select is:

SELECT t1.name, t2.date_from, t2.curr FROM person t1, TABLE(t1.history) t2;

Upvotes: 1

PKey
PKey

Reputation: 3841

Cannot verify this, but you could try something like this: 

select p.name, pp.date_from, pp.current 
from person p, table(p.history) pp;

Upvotes: 2

Related Questions