Hi everybody,
I have a (hopefully) rather simple question. Assuming two tables MAIN and DETAIL and a 1:N relationship between them (a main object may have multiple details), what is the most straightforward way of creating an SQL view that selects all columns of MAIN and a column that contains all corresponding rows of DETAIL as a nested table? Or, in other words, I want to get all the data of a MAIN "object" including all its "nested objects" in a single row.
I have seen examples which create a TABLE OF DETAIL%ROWTYPE and use the MULTISET operator to query DETAIL as a nested table. However, %ROWTYPE seems to be available in PL/SQL only, and I need an SQL view.
As always, there seem to be many ways to achieve this. Since I have lots of detail tables, I want to reduce amount of glue code (like creating intermediate types, etc.) to the minimum.
Regards,
Pat