Error on a spatial view with GROUP BY clause

StefanoIacovella · ‎06-21-2012

Hi all

I am trying to use a spatial view with aggregate functions.
The view was built with this query:

SELECT
    C.COUNTRY_CODE CNTRY_CD,
    MAX (C.COUNTRY_NAME) CNTRY_NM,
    SUM (GAS) AS GAS, SUM (OIL) AS OIL,
    SUM (ENCO) AS ENCO,
    SUM (WELLCOST) AS WELLCOST,
    SUM (UEC) AS UEC,
    SDE.ST_CENTROID (SDE.ST_AGGR_UNION (SHAPE)) AS SHAPE,
    MAX (C.OBJECTID) AS OBJECTID
FROM
    ENGIS.CDB_CNCSSN C,
    EXPL.PORT_CNTRY_LEASE_4_GIS_V1@CDBPA01_ENGIS P
WHERE
    P.CNTRY_CD = C.COUNTRY_CODE
    AND P.LEASE_CD = C.LEASE_CODE
    AND C.CNCSSN_BLCK_NBR = 1
GROUP BY C.COUNTRY_CODE;

I then registered it in ArcSDE with the following command

sdelayer -o register -l UPD_XAA_PORTFOLIO_CNTRY_P_V,SHAPE -R 204 -e p -t ST_GEOMETRY -C OBJECTID,USER -u username -p password@service_name -i sde:oracle10g

ArcSDE 10.0 for Oracle10g Build 1937 Tue Aug 16 16:08:18 2011
Layer    Administration Utility
-----------------------------------------------------
Successfully Created Layer.

The record count in sqlplus return 32 rows

SQL> select count(*) from UPD_XAA_PORTFOLIO_CNTRY_P_V;

COUNT(*)
----------
        32

But when performing a feature_info I got only 16 records

C:\Users\gdladmin>sdelayer -o feature_info -l UPD_XAA_PORTFOLIO_CNTRY_P_V,SHAPE -r all -u username -p password@service_name -i sde:oracle10g

ArcSDE 10.0 for Oracle10g Build 1937 Tue Aug 16 16:08:18 2011
Layer    Administration Utility
-----------------------------------------------------
Row Id,FID,Entity Type,Annotation,Cad Data,Number of Points,Number of Parts,Number of Subparts,Self-Touching Rings,Minimum Precision,Verification

1666,1085528320,P,F,F,1,1,0,F,Basic,0
1730,1083836416,P,F,F,1,1,0,F,Basic,0
1842,1084131328,P,F,F,1,1,0,F,Basic,0
376,1083901952,P,F,F,1,1,0,F,Basic,0
947,1083971584,P,F,F,1,1,0,F,Basic,0
661,1084016640,P,F,F,1,1,0,F,Basic,0
1907,1080778752,P,F,F,1,1,0,F,Basic,0
1014,1081573376,P,F,F,1,1,0,F,Basic,0
667,1084075008,P,F,F,1,1,0,F,Basic,0
954,1083021312,P,F,F,1,1,0,F,Basic,0
1774,1084081152,P,F,F,1,1,0,F,Basic,0
929,1082435584,P,F,F,1,1,0,F,Basic,0
1830,1082808320,P,F,F,1,1,0,F,Basic,0
960,1084083200,P,F,F,1,1,0,F,Basic,0
1849,1084084224,P,F,F,1,1,0,F,Basic,0
229,1083158528,P,F,F,1,1,0,F,Basic,0

Total rows examined:          16
Total invalid shapes:          0

If I open the feature class in ArcCatalog preview it shows 32 points on the map but if I switch to table preview there are no records shown. Trying to do an info on any points retun nothing.

Am I doing some errors?

Thank you in advance for your suggestions

Cheers

Stefano

StefanoIacovella · ‎06-21-2012

I suspected ArcSDE could be troubled with not sequential values in OBJECTID FIELD, so I made anot her trial.

Removed

MAX (C.OBJECTID) AS OBJECTID

And inserted

row_number() OVER (ORDER BY MAX(C.OBJECTID)) As OBJECTID

the OBJECTID value are now 1..n

Unfortunately this does not solved my issue.

Hope someone has some suggestion

Cheers

Stefano

VinceAngelo · ‎06-21-2012

The registered rowid column does not have to be sequential. It must be reproducible
on subsequent queries, so I don't think your workaround is workable.

You've got a number of uncommon elements in this view, and most of them are going
to negatively impact performance.

I suggest you look at the output of the view in SQL*Plus, since polynomial expansion
with non-unique objectids would explain the 'sdelayer' behavior.

- V

-

StefanoIacovella · ‎06-21-2012

Angelo, thank you very much for your attention.

I did not mention in the original post but I checked uniqueness of OBJECTID in the view output

SQL>SELECT OBJECTID,COUNT(*) FROM UPD_XAA_PORTFOLIO_CNTRY_P_V GROUP BY OBJECTID HAVING COUNT(*) > 1;

no rows selected

Did I correctly understand your suggestion?

thank you for support

Cheers

Stefano

StefanoIacovella · ‎06-21-2012

The registered rowid column does not have to be sequential. It must be reproducible
on subsequent queries, so I don't think your workaround is workable.

You've got a number of uncommon elements in this view, and most of them are going
to negatively impact performance.

I suggest you look at the output of the view in SQL*Plus, since polynomial expansion
with non-unique objectids would explain the 'sdelayer' behavior.

- V

-

I did some further investigation

the issue seems related with the OBJECTID column type.

sdetable -o describe_long -t UPD_XAA_PORTFOLIO_CNTRY_P_V -u engis -p engis_mgr@SVILUPPO -i sde:oracle10g
----------------------------------------------------------------
Column Owner           : ENGIS
Column Table           : UPD_XAA_PORTFOLIO_CNTRY_P_V
Column Name            : OBJECTID
Row ID Column Type     : User Maintained
SDE Column Type        : SE_FLOAT64
Column Size            : 38
Decimal Digits         : 10
Null Allowed?          : True

I tryed to CAST it to INTEGER inside the SQL code with

CAST(MAX (C.OBJECTID) As INTEGER)

But after registering it the sdetable -o describe_long returns the same information

I know that this is usupported and not the best thing to do but I changed the SDE.COLUM_REGISTRY information with

UPDATE COLUMN_REGISTRY
   SET SDE_TYPE = 2,
       COLUMN_SIZE = 10,
       DECIMAL_DIGITS = NULL
WHERE TABLE_NAME = 'UPD_XAA_PORTFOLIO_CNTRY_P_V'
   AND OWNER = 'ENGIS'
   AND COLUMN_NAME = 'OBJECTID';

After this the sdetable -o describe_long reports

----------------------------------------------------------------
Column Owner           : ENGIS
Column Table           : UPD_XAA_PORTFOLIO_CNTRY_P_V
Column Name            : OBJECTID
Row ID Column Type     : User Maintained
SDE Column Type        : SE_INT32
Column Size            : 10
Decimal Digits         : 0
Null Allowed?          : True

and now I can access all the records in sdelayer -o feature_info and in ArcCatalog, both shapes and attributes.

C:\Users\gdladmin>sdelayer -o feature_info -l UPD_XAA_PORTFOLIO_CNTRY_P_V,SHAPE -r all -i sde:oracle10g -u **** -p ****@*****

ArcSDE 10.0 for Oracle10g Build 1937 Tue Aug 16 16:08:18 2011
Layer    Administration Utility
-----------------------------------------------------
Row Id,FID,Entity Type,Annotation,Cad Data,Number of Points,Number of Parts,Number of Subparts,Self-Touching Rings,Minimum Precision,Verification

5081,5081,P,F,F,1,1,0,F,Basic,0
1666,1666,P,F,F,1,1,0,F,Basic,0
1954,1954,P,F,F,1,1,0,F,Basic,0
1730,1730,P,F,F,1,1,0,F,Basic,0
1798,1798,P,F,F,1,1,0,F,Basic,0
1842,1842,P,F,F,1,1,0,F,Basic,0
219,219,P,F,F,1,1,0,F,Basic,0
376,376,P,F,F,1,1,0,F,Basic,0
1899,1899,P,F,F,1,1,0,F,Basic,0
947,947,P,F,F,1,1,0,F,Basic,0
1905,1905,P,F,F,1,1,0,F,Basic,0
661,661,P,F,F,1,1,0,F,Basic,0
843,843,P,F,F,1,1,0,F,Basic,0
1907,1907,P,F,F,1,1,0,F,Basic,0
1908,1908,P,F,F,1,1,0,F,Basic,0
1014,1014,P,F,F,1,1,0,F,Basic,0
5264,5264,P,F,F,1,1,0,F,Basic,0
667,667,P,F,F,1,1,0,F,Basic,0
1600,1600,P,F,F,1,1,0,F,Basic,0
954,954,P,F,F,1,1,0,F,Basic,0
1769,1769,P,F,F,1,1,0,F,Basic,0
1774,1774,P,F,F,1,1,0,F,Basic,0
1375,1375,P,F,F,1,1,0,F,Basic,0
929,929,P,F,F,1,1,0,F,Basic,0
1887,1887,P,F,F,1,1,0,F,Basic,0
1830,1830,P,F,F,1,1,0,F,Basic,0
5263,5263,P,F,F,1,1,0,F,Basic,0
960,960,P,F,F,1,1,0,F,Basic,0
684,684,P,F,F,1,1,0,F,Basic,0
1849,1849,P,F,F,1,1,0,F,Basic,0
1882,1882,P,F,F,1,1,0,F,Basic,0
229,229,P,F,F,1,1,0,F,Basic,0

Total rows examined:          32
Total invalid shapes:          0

Is there a cleaner way to inform ArcSDE about the column data type?

Cheers

Stefano

StefanoIacovella · ‎06-21-2012

....

Is there a cleaner way to inform ArcSDE about the column data type?

Cheers

Stefano

CAST(MAX(OBJECTID) AS NUMBER(38))

solved the issue without any hacking on the ArcSDE catalog

Cheers

Stefano