SQL statements

1016
12
Jump to solution
11-16-2012 04:05 AM
Corbinde_Bruin
Occasional Contributor II
Hi all,

I'm new to SQL statements and especially within ArcGIS. I'm programming an addin against a file geodatabase in in Desktop 10.1 and I'm getting this error:

An invalid SQL statement was used.
[SELECT FeatureClassAlias FROM tblFeatureClasses WHERE IsSearchable = 'Y' ORDER BY SortSequence]

It works if I take out the ORDER BY SortSequence part, but it obviously doesn't sort at all. I've looked at all the ESRI references on SQL with File geodatabases and can't see my error. It says field names may be delimited by quotes, but that hasn't helped. It also says that ORDER BY won't work if I'm searching in all fields with SELECT *, but that shouldn't be my problem.

An invalid SQL statement was used.
[SELECT "FeatureClassAlias" FROM tblFeatureClasses WHERE "IsSearchable" = 'Y' ORDER BY "SortSequence"]

I'd greatly appreciate help with this issue from someone with SQL experience. I'll also post the code if you think it will help.

Thank you,
cdebruin

  Public Sub LoadTblToCbx(inputTable As ITable, inputFieldName As String, cbxInput As System.Windows.Forms.ComboBox)                  ' Create the query filter.         Dim queryFilter As IQueryFilter2 = New QueryFilter          ' Select the fields to be returned         queryFilter.SubFields = inputFieldName          ' Set the filter to return only searchable         queryFilter.WhereClause = """IsSearchable"" = 'Y'"          ' Use the PostfixClause to sort ascending         Dim queryFilterDef As IQueryFilterDefinition2 = CType(queryFilter, IQueryFilterDefinition2)         queryFilterDef.PostfixClause = "ORDER BY ""SortSequence"""          ' Output the returned feature class name.         Dim fieldIndex As Integer = inputTable.FindField(inputFieldName)         Dim cursor As ICursor = inputTable.Search(queryFilter, True)         Dim row As IRow = cursor.NextRow()         While Not row Is Nothing             Dim field As String = Convert.ToString(row.Value(fieldIndex))             cbxInput.Items.Add(field)             row = cursor.NextRow()         End While          Marshal.FinalReleaseComObject(cursor)      End Sub
0 Kudos
1 Solution

Accepted Solutions
LanceShipman
Esri Regular Contributor
ORDER BY is supported by File Geodatabase at ArcGIS 10.0. You must include the sort field(s) in the field list.

Something like:
' Select the fields to be returned queryFilter.SubFields = inputFieldName & ", SortSequence"  ' Set the filter to return only searchable queryFilter.WhereClause = """IsSearchable"" = 'Y'"  ' Use the PostfixClause to sort ascending  Dim queryFilterDef As IQueryFilterDefinition2 = CType(queryFilter, IQueryFilterDefinition2)  queryFilterDef.PostfixClause = "ORDER BY SortSequence"

View solution in original post

0 Kudos
12 Replies
MichaelVolz
Esteemed Contributor
Try this for additional information:

http://resources.esri.com/help/9.3/ArcGISEngine/arcobjects/esriGeoDatabase/IQueryFilterDefinition.Po...

[Visual Basic 6.0]'this is an example of using the PostfixClause property to append a Order By clause to the query
Dim pQueryFilter As IQueryFilterSet pQueryFilter = New QueryFilterpQueryFilter.SubFields = "FULLNAME"pQueryFilter.WhereClause = "OBJECTID > 10"
Dim pQueryFilterDefinition As IQueryFilterDefinitionSet pQueryFilterDefinition = pQueryFilterpQueryFilterDefinition.PostFixClause = "ORDER BY FULLNAME"
Dim pFeatureCursor As IFeatureCursorSet pFeatureCursor = pFeatureClass.Search(pQueryFilter, True)
Dim codeix As Longcodeix = pFeatureCursor.FindField("FULLNAME")
Dim pFeature As IFeatureSet pFeature = pFeatureCursor.NextFeature
While Not pFeature Is Nothing    MsgBox pFeature.Value(codeix)    Set pFeature = pFeatureCursor.NextFeatureWend



Another forum example:

Dim queryFilter As IQueryFilter
Set queryFilter = new QueryFilter
queryFilter.WhereClause = "theField = 'abc'"

Dim queryFilterDef As IQueryFilterDefinition
Set queryFilterDef = queryFilter
queryFilterDef.PostfixClause = "ORDER BY sortField"
0 Kudos
Corbinde_Bruin
Occasional Contributor II
Hmm,

No luck. I tried that syntax and I'd already referenced that VB6 example.

Thanks you Mike
0 Kudos
AndrewMay
New Contributor
Do filegeodatabase support the ORDER BY function? They didn't used to in ArcGIS 9 - not sure whether they do or not in 10.

From http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//003n00000032000000:


  • File geodatabases do not support all the features and functions available for personal geodatabases. At ArcGIS 9.2, the most commonly used functions not supported by file geodatabases include DISTINCT, GROUP BY, and ORDER BY, and the set functions AVG, COUNT, MIN, MAX, and SUM are not supported outside subqueries. Support for some of these is likely to be added in future releases.

0 Kudos
Corbinde_Bruin
Occasional Contributor II
Do filegeodatabase support the ORDER BY function? They didn't used to in ArcGIS 9 - not sure whether they do or not in 10.


That's a question I was hoping someone else could answer, haha. I see it's supported in subqueries. Can I somehow use subqueries as a workaround. I don't really understand the syntax for subqueries.

Thank you for you reply Andrew
0 Kudos
MichaelVolz
Esteemed Contributor
I think this link answers your question that this is not currently possible:

http://support.esri.com/en/bugs/nimbus/TklNMDY0NDcy

Is there any chance you could call this layer from an SDE geodatabase where ORDER BY is supported?

You might try a personal geodatabase as it might also be supported there, but you would need to test that out.
0 Kudos
Corbinde_Bruin
Occasional Contributor II
We will actually be moving to an SDE environment for our current project, so I'm glad to know that it works there.

I wanted to know if it worked in File geodatabases for other/future purposes.

Thanks Mike
0 Kudos
LanceShipman
Esri Regular Contributor
ORDER BY is supported by File Geodatabase at ArcGIS 10.0. You must include the sort field(s) in the field list.

Something like:
' Select the fields to be returned queryFilter.SubFields = inputFieldName & ", SortSequence"  ' Set the filter to return only searchable queryFilter.WhereClause = """IsSearchable"" = 'Y'"  ' Use the PostfixClause to sort ascending  Dim queryFilterDef As IQueryFilterDefinition2 = CType(queryFilter, IQueryFilterDefinition2)  queryFilterDef.PostfixClause = "ORDER BY SortSequence"
0 Kudos
Corbinde_Bruin
Occasional Contributor II
ORDER BY is supported by File Geodatabase at ArcGIS 10.0. You must include the sort field(s) in the field list.


Thank you for responding Lance,

I'm not sure what you mean by field list. Perhaps you could take a quick look at my code up top and see where I went wrong? Or point me towards an example where this field list is used/documented/explained?

-cdebruin
0 Kudos
LanceShipman
Esri Regular Contributor
See the code fragment that I posted. SubField is the field list.

Lance
0 Kudos