Re: table function - get certain array item

Robert Wehofer wrote:

> Hello there!
>
> I would like to get a certain array item as a result of a selected table
> function.
>
> Example:
> select s.* from dkm_gst_text t, TABLE(t.sdogeometry.SDO_ELEM_INFO) s where
> rownum < 4;
>
> This select statement gets me the first three array items of the array
> sdogeometry.SDO_ELEM_INFO.
>
> I'm only interested in the 3rd array item. Is there a possibility to select
> only a certain item of an array?
>
> The version
>
> select s.* from dkm_gst_text t, TABLE(t.sdogeometry.SDO_ELEM_INFO) s where
> rowid = 3;
>
> doesn't work.
>
> One more question:
>
> Why does not work following statement?
>
> select s.* from dkm_gst_text t, TABLE(t.sdogeometry.SDO_ELEM_INFO) s where
> t.rownum < 2;
>
> I would like to get all array items of the first dataset in dkm_gst_text.
>
> Regards,
> Robert

Two things the first one general education. You don't understand what ROWNUM is. Read the concept docs at tahiti.oracle.com and run the demo at http://www.psoug.org ... click on Reference ... click on Pseudocolumns.

Second ... turn the statement that works into an inline view in the form of:

SELECT * FROM (
   (SELECT rownum ZZYZX, other_stuff FROM t) WHERE ZZYZX = 3;

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)