Re: querying varray
- From: malcolm arnold <malcolmarnold@xxxxxxxxx>
- To: geraldine_2@xxxxxxxxxxx
- Date: Mon, 28 Nov 2005 16:21:34 +0000
Hi Geraldine,
You can do it like this:
SQL>select id from mytab t where exists (select null from
table(t.numlist) where column_value = 90);
ID
----------
2
Hope that helps.
Malcolm.
On 28/11/05, geraldine_2@xxxxxxxxxxx <geraldine_2@xxxxxxxxxxx> wrote:
> Hi,
> I'm having trouble writing a query to return the record containing a
> particular element in the varray. In the example below, I would like to
> search for the id that contains 90 in myvarray. The query should return id=2.
> How can I do that?
>
> thanks.
>
>
> create or replace type myvarray as varray(5) of number;
> /
>
>
> create table mytab (
> id number,
> numlist myvarray
> )
> /
>
> insert into mytab values (1,myvarray(10,20,30,40,50));
> insert into mytab values (2,myvarray(60,70,80,90,100));
>
> SQL> select * from mytab;
> ID NUMLIST
> ---------- ------------------------------
> 1 MYVARRAY(10, 20, 30, 40, 50)
> 2 MYVARRAY(60, 70, 80, 90, 100)
>
>
> geraldine
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
