| 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 si_bendovi_at_hotmail.com
(if that email address didn't require changing)
On Fri, 12 Mar 1999 16:56:02 GMT, you wrote:
>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.
>
It would look like this then. the first select is when you have a good primary key, the second is when you don't. Alternatively, if you have member functions in your type, they could just return the COUNT attribute of the nested table itself:
SQL> create or replace type numArrayType as table of number; 2 /
Type created.
SQL>
SQL> create table na_demo ( x int primary key, y numArrayType )
2 nested table y store as y_tbl;
Table created.
SQL>
SQL> insert into na_demo values( 1, numArrayType(1,2,3) );
1 row created.
SQL> insert into na_demo values( 2, numArrayType(1,2) );
1 row created.
SQL> insert into na_demo values( 3, numArrayType(1) );
1 row created.
SQL> SQL> column y format a25 SQL> SQL> select * from na_demo a 2 where 2 = ( select count(*) from 3 THE ( select y from na_demo b where b.x = a.x ) )4 /
X Y
---------- -------------------------
2 NUMARRAYTYPE(1, 2)
SQL>
SQL> select * from na_demo a
2 where 2 = ( select count(*) from
3 THE ( select y from na_demo b where b.rowid = a.rowid ) )4 /
X Y
---------- -------------------------
2 NUMARRAYTYPE(1, 2)
[snip]
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |