Re: Order By in Nested Tables
From: joel garry <joel-garry_at_home.com>
Date: Wed, 2 Nov 2011 10:12:50 -0700 (PDT)
Message-ID: <29ae641a-4af2-4e99-a3d5-f2dc2a38dca4_at_t38g2000prg.googlegroups.com>
On Nov 2, 6:48 am, ExecMan <artme..._at_yahoo.com> wrote:
> Hi,
>
> Our application has a complex query. The query retrieves data from a
> table, which contains some nested tables. I'm being asked if the
> results of the nested tables can themselves be sorted. So, the query
> is something like this:
>
> SELECT .........
> FROM stock_info s, TABLE(estimate_trends) s1,
> TABLE(financial_overview) s2
> WHERE ticker = 'IBM';
>
> They want the results for the nested tables to themselves be sorted by
> their first column, which is a sequence number.
>
> Can this be done via normal SQL?
Date: Wed, 2 Nov 2011 10:12:50 -0700 (PDT)
Message-ID: <29ae641a-4af2-4e99-a3d5-f2dc2a38dca4_at_t38g2000prg.googlegroups.com>
On Nov 2, 6:48 am, ExecMan <artme..._at_yahoo.com> wrote:
> Hi,
>
> Our application has a complex query. The query retrieves data from a
> table, which contains some nested tables. I'm being asked if the
> results of the nested tables can themselves be sorted. So, the query
> is something like this:
>
> SELECT .........
> FROM stock_info s, TABLE(estimate_trends) s1,
> TABLE(financial_overview) s2
> WHERE ticker = 'IBM';
>
> They want the results for the nested tables to themselves be sorted by
> their first column, which is a sequence number.
>
> Can this be done via normal SQL?
SQL is relational, nested tables are object-relational extensions to oracle. So, I don't know.
See http://technology.amis.nl/blog/1217/sorting-plsql-collections-the-quite-simple-way-part-two-have-the-sql-engine-do-the-heavy-lifting (found by googling oracle sort results from nested table) and don't be put off by the subject of that post, check out how the sort is done.
jg
-- _at_home.com is bogus. "ORA-01536 space quote exceeded for tablespace string" - 11.2 docsReceived on Wed Nov 02 2011 - 12:12:50 CDT