Re: Oracle8 nested table flatten query

From: <si_bendovi_at_hotmail.com>
Date: Fri, 12 Mar 1999 16:56:02 GMT
Message-ID: <7cbgur$k3n$1@nnrp1.dejanews.com>

In article <36f7268c.30523931_at_192.86.155.100>,   tkyte_at_us.oracle.com wrote:
> 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.

I understand this concept, but what about if I need to select records by condition for nested table? I mean something like:

SQL> select b colb from b where 2 = ( select count( *) from colb); select b colb from b where 2 = ( select count( *) from colb)

                                                       *

ERROR at line 1:
ORA-00942: table or view does not exist

It could be for example all clients having exactly two invoices.

> >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
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Mar 12 1999 - 10:56:02 CST