Are complex SQL queries supported in ArcObjects?

[GregRieck]

Hello,

I'm wondering if a query like this is supported in ArcObjects?

string myquery = string.Format("Select p.portid, route_infoid From (Select route_infoid, x_id From route_items Where x_table = 'port') rit Inner Join (Select portid From port Where x_table = 'fibermngr' And x_id = {0}) p On p.portid = rit.x_id Group By p.portid, rit.route_infoid", XID);

Is it supported in all database formats, personal geodatabase, file geodatabase, sql and oracle? How do I go about using such a query? What objects support it? Are relationship classes required on the tables / data using such a query?

G

[AlexanderGray]

as far as sub-queries go:

Coverages, shapefiles, and other nongeodatabase file-based data sources do not support subqueries. Subqueries that are performed on versioned ArcSDE feature classes and tables will not return features that are stored in the delta tables. File geodatabases provide the limited support for subqueries explained in this section, while personal and ArcSDE geodatabases provide full support. For information on the full set of subquery capabilities of personal and ArcSDE geodatabases, refer to your DBMS documentation.

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//00s500000033000000.htm

SQL server and Oracle will have their own syntax for complex queries.

Inner join is talked about here:
http://resources.arcgis.com/en/help/main/10.1/index.html#//018s0000000t000000

[GregRieck]

Alexander,

Thank you for the information. It is what I expected to hear. I guess I was hoping that ESRI had progressed but alas they have not.

Do you have any suggestions for possible work a rounds? Is it possible to "nest" IQueryDef queries in such a way to accomplish the same results as sub queries? Is it better to create Query Tables? Are there any solutions for large databases? Having to do separate queries on tables and parse the data for the same result as using sub queries seems time consuming. Are there suggestions for "committing" versioned data prior to performing such queries? You found -

Subqueries that are performed on versioned ArcSDE feature classes and tables will not return features that are stored in the delta tables

But what does that mean? Does that mean that the results won't include data from other versions but data within the current version will be returned? What are the possible solutions here? I would think that others have need to perform these types of queries. How have others solved this problem?

g

[AlexanderGray]

When versioned data is edited, all the inserts, deletes and update are placed in two tables for the particular feature class, the adds and deletes table (update is a delete of the row and an add of the same row with the new value,)  these are the delta tables (delta for changes.)  The feature class is made to look like a single table through queries that uses the states table and the state lineage among others.  The sde schema has changed in different versions.  The point is only when changes are pushed into the default version and the default version is compressed do the changes get moved from the delta to the base tables.  Of course once that is done you can use the Oracle or SQL server client to perform regular SQL queries and by-pass arcObjects completely.  You can also make multi-version views in the database, which pre-cans the SQL needed to reconcile the versions.  Personnally, I have found that if versioning is in the mix, de-normalizing your schema works best.  It makes dbas crazy but it makes GIS work easier and it makes ArcGIS perform better.  If your work is not GIS-centric, I would consider using un-versioned editing or only versioning the tables that really need to.

[GregRieck]

So, if I recall correctly when queries are performed using ArcObjects the results do look at the delta tables, is that right?

[AlexanderGray · Accepted solution]

So, if I recall correctly when queries are performed using ArcObjects the results do look at the delta tables, is that right?

Yes they will look at the version of the workspace and recombine the tables.  This makes a simple three or four table join blow up into full pages of SQL.  But the documentation says that sub-queries will not look at the delta tables.

[GregRieck]

Alexander,

Thank you for chatting with me. All the information is exactly what I thought it would be. You've been very helpful in confirming my knowledge on how querying works with ArcObjects.

Greg