Create a Nested Table or a dynamic table

[alvaroCab <al...@hotmail.com>]

Is there a way to create something similar to this in Apache Derby?

create or replace type item as object (
  item_id Number  ( 6  ),
  descr   varchar2(30  ),
  quant   Number  ( 4,2)
);

create or replace type items as table of item;

create table bag_with_items (
  bag_id                   number(7)     primary key,
  bag_name                 varchar2(30)  not null,
  the_items_in_the_bag     items         
)
nested table the_items_in_the_bag store as bag_items_nt;

If not.. is it possible to create a List row or something like this where I
could put the keys of another tables?

Thanks to all!


-- 
View this message in context: http://old.nabble.com/Create-a-Nested-Table-or-a-dynamic-table-tp27010524p27010524.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Create a Nested Table or a dynamic table

[Rick Hillegas <Ri...@Sun.COM>]

Hi Alvaro,

Derby does not currently support structured types or typed tables. 
However, we are adding user defined types to Derby. Hopefully this 
feature will be ready for the 10.6 release later this year. User defined 
types (UDTs) give you the ability to store Java objects in columns and 
use Java objects as function/procedure parameters and return values. 
DERBY-651 tracks our work on this issue. What we have in the trunk right 
now should be complete enough for you to run experiments. Hopefully the 
functional spec attached to DERBY-651 will give you all the information 
you need--if not, I'd be happy to help you get started. Your feedback is 
appreciated.

Thanks,
-Rick


alvaroCab wrote:
> Is there a way to create something similar to this in Apache Derby?
>
> create or replace type item as object (
>   item_id Number  ( 6  ),
>   descr   varchar2(30  ),
>   quant   Number  ( 4,2)
> );
>
> create or replace type items as table of item;
>
> create table bag_with_items (
>   bag_id                   number(7)     primary key,
>   bag_name                 varchar2(30)  not null,
>   the_items_in_the_bag     items         
> )
> nested table the_items_in_the_bag store as bag_items_nt;
>
> If not.. is it possible to create a List row or something like this where I
> could put the keys of another tables?
>
> Thanks to all!
>
>
>   

Re: Create a Nested Table or a dynamic table

[Sylvain Leroux <sy...@chicoree.fr>]

Hi Alvaro,

Depending of your needs, using two tables and a foreign key could suffice? 
Something like that:

create table bag_with_items (
    bag_id                   number(7)     primary key,
    bag_name                 varchar2(30)  not null,
    ...
)

create table Item (
    bag_id                   number(7) REFERENCES bag_with_items(bag_id),

    item_id Number  ( 6  ),
    descr   varchar2(30  ),
    quant   Number  ( 4,2)
)

But maybe I missed something important?

Sylvain.

alvaroCab a écrit :
> 
> Is there a way to create something similar to this in Apache Derby?
> 
> create or replace type item as object (
>   item_id Number  ( 6  ),
>   descr   varchar2(30  ),
>   quant   Number  ( 4,2)
> );
> 
> create or replace type items as table of item;
> 
> create table bag_with_items (
>   bag_id                   number(7)     primary key,
>   bag_name                 varchar2(30)  not null,
>   the_items_in_the_bag     items         
> )
> nested table the_items_in_the_bag store as bag_items_nt;
> 
> If not.. is it possible to create a List row or something like this where I
> could put the keys of another tables?
> 
> Thanks to all!
> 
> 


-- 
sylvain@chicoree.fr
http://www.chicoree.fr