I'm trying to pass in a geometry(polygon) collected as an ESRI.ArcGIS.Client.Geometry.Geometry type on a map and passing it as an IN parameter into an oracle sql query. I'm also trying to use st_intersects to find features that intersect the passed-in geometry.
I can't seem to get my syntax correct. Here is my sql query:
WITH cons AS (SELECT DISTINCT culvertgid , condition AS condition_code, value AS condition_literal FROM culvert_inspect LEFT OUTER JOIN (SELECT Extractvalue (CodedValues.column_value, 'CodedValue/Code') AS Code, Extractvalue (CodedValues.column_value, 'CodedValue/Name') AS VALUE FROM sde.gdb_items_vw items inner join sde.gdb_itemtypes itemtypes ON items.TYPE = itemtypes.uuid, TABLE ( Xmlsequence (Xmltype (DEFINITION).EXTRACT ( '/GPCodedValueDomain2/CodedValues/CodedValue'))) CodedValues WHERE itemtypes.name = 'Coded Value Domain' AND items.name = 'OVERALL_CONDITION') coded_values ON culvert_inspect.condition = coded_values.code) SELECT DISTINCT ci.condition_code, condition_literal FROM culverts c INNER JOIN cons ci ON ( c.globalid_1 = ci.culvertgid ) WHERE sde.st_intersects(c.shape, sde.ST_Polygon (-10685820.278500814 5060357.6856116494, -10685820.278500814 5101327.9327725023, -10645461.527566245 5101327.9327725023, -10645461.527566245 5060357.6856116494, -10685820.278500814 5060357.6856116494)) = 1; ORDER BY condition_code
The code before the WHERE statement gets the domain descriptions from a table called culvert_inspect. What I'm trying to do is return only the domain values that are in the features that are within the geometry that is getting passed in. Everything works up until the WHERE statement. I've tested this same query and instead of passing in geometry, I pass in a value from a field and return expected results.