Home » Server Options » Spatial » UNION operator with SDO_GEOMETRY column (Oracle 10g Release 10.2.0.4.0, Windows)
UNION operator with SDO_GEOMETRY column [message #545424] Wed, 29 February 2012 06:33 Go to next message
yuko
Messages: 65
Registered: August 2011
Member
Hi All,

Could you please help me with the below requirement.

--Test Case
CREATE TABLE t1 
(
  id number, 
  ftr_type VARCHAR2(10), 
  geom SDO_GEOMETRY
);

CREATE TABLE t2 
(
  id number, 
  ftr_type VARCHAR2(10), 
  geom SDO_GEOMETRY
);


--Sample Data
INSERT INTO t1(id,ftr_type,geom) VALUES 
(1,
'CABLE',
MDSYS.SDO_GEOMETRY
 (
  2002,
  null,
  null,
  MDSYS.SDO_ELEM_INFO_ARRAY (1,2,1),
  MDSYS.SDO_ORDINATE_ARRAY (10,10, 20,25, 30,10, 40,10)
 )
);

INSERT INTO t1(id,ftr_type,geom) VALUES 
(2,
'F_CABLE',
MDSYS.SDO_GEOMETRY
 (
  2002,
  null,
  null,
  MDSYS.SDO_ELEM_INFO_ARRAY (1,2,1),
  MDSYS.SDO_ORDINATE_ARRAY (1,1, 2,5, 3,7, 4,8)
 )
);

/***********************************/
INSERT INTO t2(id,ftr_type,geom) VALUES 
(3,
'CABLE',
MDSYS.SDO_GEOMETRY
 (
  2002,
  null,
  null,
  MDSYS.SDO_ELEM_INFO_ARRAY (1,2,1),
  MDSYS.SDO_ORDINATE_ARRAY (1,1, 2,2)
  )
);

INSERT INTO t2(id,ftr_type,geom) VALUES 
(4,
'F_CABLE',
MDSYS.SDO_GEOMETRY
 (
  2002,
  null,
  null,
  MDSYS.SDO_ELEM_INFO_ARRAY (1,2,1),
  MDSYS.SDO_ORDINATE_ARRAY (3,3, 4,4,5,5,6,6)
 )
);


I have to create a view by filtering the CABALES (FTR_TYPE = 'CABLE') from T1 and T2.

SQL> CREATE OR REPLACE VIEW CABLE
  2  AS
  3  SELECT id, ftr_type,geom FROM t1 WHERE ftr_type = 'CABLE'
  4  UNION
  5  SELECT id, ftr_type,geom FROM t2 WHERE ftr_type = 'CABLE'
  6  /
View created.


After creating the view, when I try to query the data from the view, I get the below error message.
SQL> select * from cable;
select * from cable
*
ERROR at line 1:
ORA-22901: cannot compare nested table or VARRAY or LOB attributes of an object type


Could you please suggest me the solution for this.

Thank you,
Yuko
Re: UNION operator with SDO_GEOMETRY column [message #545527 is a reply to message #545424] Thu, 01 March 2012 00:47 Go to previous messageGo to next message
_jum
Messages: 490
Registered: February 2008
Senior Member
You should use UNION ALL.

[Updated on: Thu, 01 March 2012 00:47]

Report message to a moderator

Re: UNION operator with SDO_GEOMETRY column [message #545548 is a reply to message #545527] Thu, 01 March 2012 03:10 Go to previous message
yuko
Messages: 65
Registered: August 2011
Member
Thank you for the solution.
Previous Topic: Error in passing geometry column to SQL query
Next Topic: Introduction to Oracle Spatial
Goto Forum:
  


Current Time: Fri Nov 28 12:23:13 CST 2014

Total time taken to generate the page: 0.10557 seconds