| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle8 nested table flatten query
A copy of this was sent to sbenda_at_my-dejanews.com
(if that email address didn't require changing)
On Thu, 11 Mar 1999 18:48:51 GMT, you wrote:
>Hello,
>
>I need to select all records from nested table. Below is set of statements
>ended by error. In fact what I need is:
>
you cannot. Each row of a table that contains a nested table points to a 'virtual' table. Each and every row has a "virtually unique" table it points to. Thats the whole concept behind the nested table...
You have to pick exactly 1 nested table to 'flatten'. Thats why the first query with rownum=1 works.
>select * from bb;
>
>bb is table where nested table is stored. But there is impossible to reference
>nested table. Any idea? What I missed up?
>
>Please post to me as well.
>
>Stanislav Benda
>email:si_bendovi_at_hotmail.com
>
>
>SQL> select b from b;
>
>B(A, B)
>-----------------------------------------------------------------------------
>--- -------------------- TB(TA(1, 1), TA(2, 3)) TB(TA(1, 1), TA(2, 2), TA(3,
>3)) TB(TA(1, 1), TA(2, 2), TA(3, 3), TA(4, 4))
>
>SQL> select b from b where rownum = 1;
>
>B(A, B)
>-----------------------------------------------------------------------------
>--- -------------------- TB(TA(1, 1), TA(2, 3))
>
>SQL> select * from the ( select b from b where rownum = 1);
>
> A B
>--------- ---------
> 1 1
> 2 3
>
>SQL> select * from the ( select b from b);
>select * from the ( select b from b)
> *
>ERROR at line 1:
>ORA-01427: single-row subquery returns more than one row
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |