Re: Oracle Spatial SDO Aggregate Union fails with ORA-29913 ODCIAGGREGATEMERGE
Date: Mon, 16 Jun 2008 00:16:38 -0700 (PDT)
Message-ID: <3ec08178-324b-4fd4-a584-7f23c267373d@l64g2000hse.googlegroups.com>
On Jun 12, 6:53 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:
> -----------------------------------------------------------------
> Rethinking the case, for a test: what happens when you replace the left
> outer join with an inner join? (Besides not getting all the results)
>
> Shakespeare
Progress! :D
But, when i rewrite the query i get the following error: SQL*Plus: Release 9.2.0.1.0 - Production on Ma Jun 16 09:08:07 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Voer wachtwoord in:
Verbonden met:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
SQL> SELECT
2 DISTINCT(GUID),
3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
GEOMETRIE
4 FROM (
5 -- with parent
6 SELECT 7 DISTINCT(GUID), 8 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE 9 FROM 10 ( 11 SELECT 12 MULTI.PARENT AS GUID, 13 TOPO.GEOMETRIE AS GEOMETRIE 14 FROM DGDTW_TOPOGRAFIE TOPO 15 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 16 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 17 INNER JOIN DGDTW_PARENT_CHILD MULTI 18 ON MULTI.CHILD = GUIDS.ID 19 AND MULTI.ARCHIVE IS NULL 20 WHERE TOPO.VERVAL IS NULL 21 AND NOT TOPO.PARAMETERS = 515 22 ORDER BY GUID 23 ) LINES 24 GROUP BY GUID
25 UNION ALL
26 -- without parent
27 ( 28 SELECT 29 DISTINCT(GUID), 30 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE 31 FROM 32 ( 33 SELECT 34 GUIDS.ID AS GUID, 35 TOPO.GEOMETRIE AS GEOMETRIE 36 FROM DGDTW_TOPOGRAFIE TOPO 37 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 38 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 39 WHERE TOPO.VERVAL IS NULL 40 AND NOT TOPO.PARAMETERS = 515 41 AND GUIDS.ID NOT IN (SELECT CHILD FROM DGDTW_PARENT_CHILD) 42 ORDER BY GUID 43 ) LINES 44 GROUP BY GUID 45 )
46 ) UNIONLINES
47 GROUP BY GUID
48 ;
ERROR:
ORA-03113: Einde-van-bestand op communicatiekanaal.
Er zijn geen rijen geselecteerd.
SQL>
The following query works, so something goes wrong on doing the
distinct over both union results. Any thoughts?
SELECT
DISTINCT(GUID),
COUNT(*)
FROM (
- with parent SELECT DISTINCT(GUID), SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE FROM ( SELECT MULTI.PARENT AS GUID, TOPO.GEOMETRIE AS GEOMETRIE FROM DGDTW_TOPOGRAFIE TOPO INNER JOIN DGDTW_OBJECTGUIDS GUIDS ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID INNER JOIN DGDTW_PARENT_CHILD MULTI ON MULTI.CHILD = GUIDS.ID AND MULTI.ARCHIVE IS NULL WHERE TOPO.VERVAL IS NULL AND NOT TOPO.PARAMETERS = 515 ORDER BY GUID ) LINES GROUP BY GUID UNION ALL
- without parent ( SELECT DISTINCT(GUID), SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE FROM ( SELECT GUIDS.ID AS GUID, TOPO.GEOMETRIE AS GEOMETRIE FROM DGDTW_TOPOGRAFIE TOPO INNER JOIN DGDTW_OBJECTGUIDS GUIDS ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID WHERE TOPO.VERVAL IS NULL AND NOT TOPO.PARAMETERS = 515 AND GUIDS.ID NOT IN (SELECT CHILD FROM DGDTW_PARENT_CHILD) ORDER BY GUID ) LINES GROUP BY GUID ) ) UNIONLINES GROUP BY GUID )