Oracle-L: RE: selecting the nth value from a varray column

From: Szecsy Tamas <tszecsy_at_GEOMETRIA.hu>
Date: Wed, 13 Mar 2002 03:53:22 -0800
Message-ID: <F001.00426CA0.20020313035322@fatcity.com>

I give an example, just to make clear what I mean:

How do I retrieve coordinate values from Oracle Spatial Object model?

Create a table: create table sdo (geometry mdsys.sdo_geometry);
Insert a record with coordinate values ( 19.135147 and 47.021199 are the coordinates) insert into sdo values(mdsys.sdo_geometry(2001,NULL,NULL, mdsys.sdo_elem_info_array(1, 0, 6000, 4, 1, 1), mdsys.sdo_ordinate_array(.000013146, 2.1416E-07, -.00001947, 19.135147, 47.021199)) );

Is there a method to retrieve the coordinate values with single select statement similar to this

select s.geometry<something1> "x_coord", s.geometry<something2> "y_coord" from sdo s;

hopefully resulting this:

x_coord y_coord

19.135147 47.021199

Thanks,

Tamas Szecsy

-----Original Message-----

Sent: Wednesday, March 13, 2002 11:23
To: Multiple recipients of list ORACLE-L

Hi,

could someone please help me with this? I have a table where a column is of type varray of numbers. I need the 4th and 5th values that are coordinate values. How can I select only these columns` value?

Every help will be highly appreciated.

Tamas Szecsy
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Szecsy Tamas
INET: tszecsy_at_GEOMETRIA.hu

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------


To REMOVE yourself from this mailing list, send an E-Mail message

I hope I aunderstood your issue see this:

rem -----------------------------------------------------------------------

rem Filename: maxvalue.sql
rem Purpose: Select the Nth highest value from a table

rem Date:       18-Apr-2001
rem Author:     Deepak Rai, SSE, Satyam Computer Services Ltd. India
rem -----------------------------------------------------------------------

select level, max('col_name') from my_table where level = '&n'
connect by prior ('col_name') > 'col_name') group by level;

Example :
--
Given a table called emp with the following columns:
id number
name varchar2(20)
sal number
--
For the second highest salary:
--
select level, max(sal) from emp
where level=2
connect by prior sal > sal
group by level
--
Ciprian MAFTEI, Romania
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ciprian Maftei INET: mac_at_telequote.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------


To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Szecsy Tamas
INET: tszecsy_at_GEOMETRIA.hu

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------


To REMOVE yourself from this mailing list, send an E-Mail message