Building a nest SQL

create type it_sort_of_defeats_the_point as table of integer;

create table you_can_store_your_columns (
  as_nested_tables_but it_sort_of_defeats_the_point
nested table as_nested_tables_but 
store  as    of_using_a_relational_database

-- populate the nested table
  yc  it_sort_of_defeats_the_point := it_sort_of_defeats_the_point(1, 2, 3);
  insert into you_can_store_your_columns values (yc);
  yc  := it_sort_of_defeats_the_point(4, 5);
  insert into you_can_store_your_columns values (yc);

-- the table() operator can be used to extract the values out of the 
select c.* from you_can_store_your_columns, table(as_nested_tables_but) c
where  c.column_value = 1;

drop table you_can_store_your_columns purge;
drop type it_sort_of_defeats_the_point;

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>