select by attribute with shape fields on sde layer

[laurencecuche]

Hi all,

i am facing a weird thing when i try to make a selection with arcmap 'select by attribute' or a 'definition query' like:

select * from myfeatureclass where shape.area > 100 and myvarcharfield = 'Hello'
-> Nothing gets selected (and it should!)

If i split the query:
select * from myfc where shape.area > 100
-> all ok
select * from myfc where myvarcharfield = 'Hello'
-> all ok


If i trace the sql on the db side i get the following statement:
....WHERE  (MYFC.SHAPE.AREA  > 100 AND MYVARCHARFIELD = 'HELLO')

To obtain the right result i must wrap the query with UPPER()
select * from myfeatureclass where shape.area > 100 and upper(myvarcharfield) = upper('Hello')

I am am not really happy with this solution because 'HELLO' and 'Hello' are NOT the same!

Did someone else experienced that?? Would that be a bug?

My setup:

Oracle 11g (Windows)
ArcSDE 10.1 SP1
Direct Connect from ArcMap 10.1
ST_Geometry

[laurencecuche]

Hi Sol,

Thank you for your hint. I'll try to investigate in this direction (as i have no better ideas) but i'm not so sur this will solve my problem.
Remember, if i run something like this, the query gets issue correctly:
select * from myfc where myvarcharfield = 'Hello'

The query doesn't get issue correctly in conjunction with any SHAPE Function (area or length)..

Regards,
Laurence

[VinceAngelo]

In what coordinate system is your data?  What units?

- V

[laurencecuche]

@ Sol:
The whole setup up (with st_geometry) used to work before we migrated to 10.1 (desktop and sde). We previously used 9.3.1.
@ Vince:
Projected Coordinate System: CH1903_LV03
Projection: Hotine_Oblique_Mercator_Azimuth_Center
Units: meters
Why?
The SHAPE Functions are working well, it's only when i mix them with another where statement involving a string that the sql statement doesn't get case sensitive anymore...

Regards & thanks for your help

[laurencecuche · Accepted solution]

So, after talking to esri Support we got a solution...

To obtain the right result (in version 10.1), one need to hack a bit the sql query in the "select by attribute" GUI and it is not intuitiv at all...

The query that would be the most intuitiv in the GUI would be the following:

>SELECT * FROM ARV_BASIS.UP_GEMEINDEN_F WHERE GEMEINDENAME ='Andelfingen' AND SHAPE.AREA > 10

But this generate the following query in oracle sde:
>SELECT OBJECTID, BFS, ARPS, BEZIRKSNAME, ART_TEXT, GEMEINDENAME, ART_CODE, AREA_ROUND, 1 SHAPE,UP_GEMEINDEN_F.SHAPE.area,UP_GEMEINDEN_F.SHAPE.len,UP_GEMEINDEN_F.SHAPE.points,UP_GEMEINDEN_F.SHAPE.numpts,UP_GEMEINDEN_F.SHAPE.entity,UP_GEMEINDEN_F.SHAPE.minx,UP_GEMEINDEN_F.SHAPE.miny,UP_GEMEINDEN_F.SHAPE.maxx,UP_GEMEINDEN_F.SHAPE.maxy,UP_GEMEINDEN_F.rowid FROM ARV_BASIS.UP_GEMEINDEN_F UP_GEMEINDEN_F WHERE (UP_GEMEINDEN_F.SHAPE.AREA > 10 AND BEZIRKSNAME = 'ANDELFINGEN')

The query that will generate the right case sensitive query is the following:

>SELECT * FROM ARV_BASIS.UP_GEMEINDEN_F WHERE GEMEINDENAME ='Andelfingen' AND UP_GEMEINDEN_F.SHAPE.AREA > 10

this will issue the query i wanted in oracle (exactely the same as above but the string is not capitalized):
>SELECT OBJECTID, BFS, ARPS, BEZIRKSNAME, ART_TEXT, GEMEINDENAME, ART_CODE, AREA_ROUND, 1 SHAPE,UP_GEMEINDEN_F.SHAPE.area,UP_GEMEINDEN_F.SHAPE.len,UP_GEMEINDEN_F.SHAPE.points,UP_GEMEINDEN_F.SHAPE.numpts,UP_GEMEINDEN_F.SHAPE.entity,UP_GEMEINDEN_F.SHAPE.minx,UP_GEMEINDEN_F.SHAPE.miny,UP_GEMEINDEN_F.SHAPE.maxx,UP_GEMEINDEN_F.SHAPE.maxy,UP_GEMEINDEN_F.rowid FROM ARV_BASIS.UP_GEMEINDEN_F UP_GEMEINDEN_F WHERE (UP_GEMEINDEN_F.SHAPE.AREA > 10 AND BEZIRKSNAME = 'Andelfingen')

As i said, it 's not really intuitiv and you won't achieve to write this query by using the tools buttons only ...

I don't know if this is a bug or a feature...

Hope this post can help someone...