0

I have used this query;

select nm.* from data nm , table (nm.class) a;

Here data is my main table and class is my nested table. But the sql statement is not working.

2
  • 1
    Do you absolutely have to use nested tables? They complicate things, as you are finding out now. If you can, I would highly recommend you switch to a traditional parent-child table setup, and then your query becomes select ... from parent_table p inner join child_table c on <join conditions> which is much simpler to understand and query. Commented Jul 26, 2018 at 10:12
  • 1
    What is the DDL for the table, can you include some sample data and the result you expect, and what does 'not working' mean? Do you get an error, or just not the result you want? The title says 'describe' which implies you want to see the structure, but your query is looking for the data., which is confusing. Commented Jul 26, 2018 at 10:59

1 Answer 1

1

What you are doing works, sort of:

create type my_nt_type as table of number
/

create table data (id number, class my_nt_type)
nested table class store as class_nt
/

insert into data values (1, my_nt_type(1, 2, 3, 4));

select nm.* from data nm , table (nm.class) a;

        ID CLASS                         
---------- ------------------------------
         1 MY_NT_TYPE(1, 2, 3, 4)        
         1 MY_NT_TYPE(1, 2, 3, 4)        
         1 MY_NT_TYPE(1, 2, 3, 4)        
         1 MY_NT_TYPE(1, 2, 3, 4)        

You are cross-joining (with old syntax) the data table with table expression from table(), but you are only referring to the original data table columns in the select list; nm.* means you see all the columns from data including however your client chooses to display the nested table, with each row repeated by however many elements there are in the nested table for that row.

You can get a more useful result by specifying the non-NT columns from data, plus the columns from the exploded nested table (or fields form its object, if it's more complicated than my example):

select nm.id, a.column_value as from_class
from data nm
cross join table (nm.class) a;

        ID FROM_CLASS
---------- ----------
         1          1
         1          2
         1          3
         1          4

With a table of objects it's only slightly more complicated, in that you need to list all the fields you want:

create type my_obj_type as object (field1 number, field2 varchar2(10))
/

create type my_nt_type as table of my_obj_type
/

create table data (id number, class my_nt_type)
nested table class store as class_nt
/

insert into data values (1, my_nt_type(my_obj_type(1, 'One'), my_obj_type(2, 'Two')));
insert into data values (2, my_nt_type(my_obj_type(3, 'Three'), my_obj_type(4, 'Four'),
  my_obj_type(5, 'Five')));

select nm.id, a.field1, a.field2
from data nm
cross join table (nm.class) a;

        ID     FIELD1 FIELD2    
---------- ---------- ----------
         1          1 One       
         1          2 Two       
         2          3 Three     
         2          4 Four      
         2          5 Five      
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.