I'm trying to add an Oracle view (Oracle 11g) to GeoServer 2.1.1 but
the columns of the view are being replicated multiple times - the
query that GeoServer is running is:
SELECT * FROM (SELECT
SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS,SYSTEMCODENUMBER,SHORTDESCRIPTION,LONGDESCRIPTION,DATASOURCE_TYPEID,NORTHING,EASTING,GEOMETRY
as GEOMETRY,NETWORKPATHREFERENCE,TRANSPORTLINKREFERENCE,TYPEID,LINKDISTANCE,CREATIONDATE,DELETIONDATE,QUALITYSTATEMENTID,DISPLAYOPTION,STR_STATUS
FROM DETECTOR_DEFINITION WHERE SDO_RELATE(GEOMETRY, ?,
'mask=anyinteract querytype=WINDOW') = 'TRUE' ) WHERE ROWNUM <= 50
which is clearly wrong.
The view is defined as:
CREATE OR REPLACE FORCE VIEW "ADMINUTMC"."DETECTOR_DEFINITION" (
"SYSTEMCODENUMBER", "SHORTDESCRIPTION", "LONGDESCRIPTION",
"DATASOURCE_TYPEID", "NORTHING", "EASTING", "GEOMETRY",
"NETWORKPATHREFERENCE", "TRANSPORTLINKREFERENCE", "TYPEID", "LINKDISTANCE",
"CREATIONDATE", "DELETIONDATE", "QUALITYSTATEMENTID", "DISPLAYOPTION",
"STR_STATUS", primary key(SYSTEMCODENUMBER)RELY DISABLE NOVALIDATE)
AS
SELECT
SYSTEMCODENUMBER,
SHORTDESCRIPTION,
LONGDESCRIPTION,
DATASOURCE_TYPEID,
NORTHING,
EASTING,
GEOMETRY,
NETWORKPATHREFERENCE,
TRANSPORTLINKREFERENCE,
TYPEID,
LINKDISTANCE,
CREATIONDATE,
DELETIONDATE,
QUALITYSTATEMENTID,
DISPLAYOPTION,
STR_STATUS
FROM
ADMINUTMC.TBLDETECTORDEFINITION;
Has anyone seen anything liek this before?
Ian
--
Ian Turton