Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2004
    Posts
    16

    Unanswered: How to get data from nested tables using the '.' operation

    Hi!!!!!

    I am not able to retrieve the single column data from the nested tables and would like to take help from experts.
    The below are the steps how I created the table and using it.

    create type ord_ty as object (itemcode varchar2(5), qty_ord number(5), qty_delivd number(5));
    /

    create type ord_nt as table of ord_ty;
    /

    create table order_master (orderno varchar2(5), odate date, vencode varchar2(5), dets ord_nt) nested dets stored as ord_nt_tab;
    /

    inserting the values into order_master

    insert into order_master
    values
    ( 'o100',sysdate,'v100',
    ord_nt ( ord_ty('i100',12,10),
    ord_ty('i101',13,2),
    ord_ty('i102',20,10)
    )
    );

    I am able to get the data from dets column as

    select * from table (select t.dets from order_master t where orderno = 'o100');

    Now I want to retrieve only the itemcodes that belong to order 'o100' only.
    The result of the query should be

    i100
    i101
    i102

    can anyone write the query for me.Thanks in advance.

    Regards
    sudhav17

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    How I detest tables based on nested tables! I do hope you are only doing this for amusement, you would not really build tables like this? That would be all pain and no gain...

    Anyway:
    Code:
    SQL> select itemcode from table (
      2  select dets from order_master
      3  where orderno='o100'
      4  );
    
    ITEMC
    -----
    i100
    i101
    i102
    BTW, your CREATE TABLE statement was wrong: I had to fix it like this:

    create table order_master (orderno varchar2(5), odate date, vencode varchar2(5), dets ord_nt)
    nested table dets store as ord_nt_tab

  3. #3
    Join Date
    Sep 2004
    Posts
    16
    Thanks a lot Mr. Andrewst for the your query and as well correcting me.
    I was not doing all this for amusement, but was learning how to implement oops database concepts in my testing schema.

    Thanks again
    sudhav17

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by sudhav17
    I was not doing all this for amusement, but was learning how to implement oops database concepts in my testing schema.
    What I meant was, are you doing it just to learn the concept, or do you intend to implement such tables in a real, production system? I was hoping that you would not implement such tables in a production system!

  5. #5
    Join Date
    Sep 2013
    Posts
    1
    Quote Originally Posted by sudhav17 View Post
    Hi!!!!!

    I am not able to retrieve the single column data from the nested tables and would like to take help from experts.
    The below are the steps how I created the table and using it.

    create type ord_ty as object (itemcode varchar2(5), qty_ord number(5), qty_delivd number(5));
    /

    create type ord_nt as table of ord_ty;
    /

    create table order_master (orderno varchar2(5), odate date, vencode varchar2(5), dets ord_nt) nested dets stored as ord_nt_tab;
    /

    inserting the values into order_master

    insert into order_master
    values
    ( 'o100',sysdate,'v100',
    ord_nt ( ord_ty('i100',12,10),
    ord_ty('i101',13,2),
    ord_ty('i102',20,10)
    )
    );

    I am able to get the data from dets column as

    select * from table (select t.dets from order_master t where orderno = 'o100');

    Now I want to retrieve only the itemcodes that belong to order 'o100' only.
    The result of the query should be

    i100
    i101
    i102

    can anyone write the query for me.Thanks in advance.

    Regards
    sudhav17
    -------------------------------
    Dear sudhav17,

    You can write the query in the following way:

    select d.*
    from order_master o, table(o.dets) d;

    select d.itemcode
    from order_master o, table(o.dets) d;

    select d.itemcode
    from order_master o, table(o.dets) d
    where d.itemcode = 'o100';


    Regards,
    Sayantan

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Sayantan,

    Do you believe that your post will assist sudhav17 after almost NINE years?

    why resurrect old zombie thread as your first post here?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Mar 2013
    Posts
    8
    Quote Originally Posted by anacedent View Post
    Sayantan,

    Do you believe that your post will assist sudhav17 after almost NINE years?

    why resurrect old zombie thread as your first post here?
    Why be negative to someone who is trying to help? I'm glad Sayantan posted, it helped me.

  8. #8
    Join Date
    Mar 2013
    Posts
    8
    Quote Originally Posted by sayantan_dev View Post
    -------------------------------
    Dear sudhav17,

    You can write the query in the following way:

    select d.*
    from order_master o, table(o.dets) d;

    select d.itemcode
    from order_master o, table(o.dets) d;

    select d.itemcode
    from order_master o, table(o.dets) d
    where d.itemcode = 'o100';


    Regards,
    Sayantan

    Thanks Sayantan, this helped me understand how to get data out of a nested table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •