8i: querying nested tables with 'order by'

Hi,

we are having trouble querying nested tables with 'order by' (Oracle 8i on Solaris).

We use the following queries:

select * from doptest; -- ok
select cast(multiset(select a, b from table(d.dopval)) as dopnt) as test from doptest d; -- ok
select cast(multiset(select a, b from table(d.dopval) order by a, b) as dopnt) as test from doptest d; -- ok
select cast(multiset(select a, b from table(d.dopval) order by a) as dopnt) as test from doptest d; -- not ok select cast(multiset(select a, b from table(d.dopval) order by b) as dopnt) as test from doptest d; -- not ok

Has anyone any experience/solution for this problem ??

All necessary definitions for the example to run are appended below.

Carsten Reinhard,
University of Hannover, Department of Mathematics and Computer Science.

• definitions used: create type doppel as object (a number, b number);
  /
  create type dopnt as table of doppel;
  /
  

create table doptest (dopval dopnt) nested table dopval store as dopntab;

insert into doptest values (dopnt(doppel(2, 3), doppel(4, 5), doppel(0, 4)));

insert into doptest values (dopnt(doppel(7, 3), doppel(5,1))); Received on Tue Oct 12 1999 - 03:34:43 CDT