Hi,
My version is 11.2.0.4 SE.
I'm trying to figure out a way to order a query result involving values extracted from a nested table column. I know of the pseudocolumn COLUMN_VALUE which lets you access the value of a nested table, but there doesn't seem to be an equivalent COLUMN_INDEX that tells you about its subscript.
Here is a test case to try:
create type num_array_typ is table of number;
/
create table t(id integer, num_array num_array_typ) nested table num_array store as num_array_nt;
insert into t values(1, num_array_typ(1, 2));
insert into t values(2, num_array_typ(5, 3, 4));
commit;
If I run the following query, the result is displayed the way I want:
SQL> select t.id, n.column_value
from t,
table(t.num_array) n;
ID COLUMN_VALUE
---------- ------------
1 1
1 2
2 5
2 3
2 4
Looks like it's ordered by the subscripts for each id. But since we know a query result order is only guaranteed by the ORDER BY clause, what can I use for COLUMN_VALUE? If I use :
SQL> select t.id, n.column_value
from t,
table(t.num_array) n
order by 1;
ID COLUMN_VALUE
---------- ------------
1 2
1 1
2 3
2 4
2 5
SQL>
You can see COLUMN_VALUE results have been rearranged randomly.
Is there a way to keep the values according to their subscript positions?
Thanks