Use SQL functions with IQueryDef

Summary

You can use SQL functions, such as MIN, MAX, SUM, and DISTINCT, with IQueryDef to retrieve information about column values. You cannot use SQL functions with IQueryFilter or ISpatialFilter.

You can improve performance of processing large files, up to 100 times depending on the RDBMS and server resources, by using native SQL function instead of an IDataStatistics object. The down side of using SQL native function is that this will only work against an RDBMS; it will fail with shapefiles and ArcInfo coverages.

You can use the IQueryDef::WhereClause method to supply selection logic. Versioned data is supported.

Procedure

• Establish a connection to the geodatabase for each example and create the QueryDef.
  
  

  Code:
      Dim pWorkspaceFactory As IWorkspaceFactory
      
      Set pWorkspaceFactory = New SdeWorkspaceFactory
      
      Dim pPropertySet As IPropertySet
      Set pPropertySet = New PropertySet
      With pPropertySet
          .SetProperty "Server", "testserver"
          .SetProperty "Instance", "5253"
          .SetProperty "Database", ""
          .SetProperty "user", "testuser"
          .SetProperty "password", "testpassword"
          .SetProperty "version", "sde.defaults"
      End With
      
      ' Open the connection to SDE
      Set pWorkspace = pWorkspaceFactory.Open(pPropertySet, Me.hWnd)
      
      Dim pFeatureWorkspace As IFeatureWorkspace
      Set pFeatureWorkspace = pWorkspace
      
      Dim pQueryDef As IQueryDef
      Set pQueryDef = pFeatureWorkspace.CreateQueryDef

  
  
• To find the min:
  
  

  Code:
      pQueryDef.Tables = "parcels"
      pQueryDef.SubFields = "min (parcels_id)"
      pQueryDef.WhereClause = ""
      
      Dim pCursor As ICursor
      Set pCursor = pQueryDef.Evaluate
      
      Dim pRow As IRow
      Set pRow = pCursor.NextRow
      
      Debug.Print pRow.Value(0)
  

  
  
• To find the max:
  
  

  Code:
      pQueryDef.Tables = "parcels"
      pQueryDef.SubFields = "max (parcels_id)"
      pQueryDef.WhereClause = ""
      
      Dim pCursor As ICursor
      Set pCursor = pQueryDef.Evaluate
      
      Dim pRow As IRow
      Set pRow = pCursor.NextRow
      
      Debug.Print pRow.Value(0)
  

  
  
• To return distinct values:
  
  

  Code:
      pQueryDef.Tables = "counties2"
      pQueryDef.SubFields = "distinct (state_name)"
      pQueryDef.WhereClause = ""
      
      Dim pCursor As ICursor
      Set pCursor = pQueryDef.Evaluate
      
      Dim pRow As IRow
      Set pRow = pCursor.NextRow
      
      Dim iRecCount As Integer
      iRecCount = 0
      
      Do
          Debug.Print pRow.Value(0)
          iRecCount = iRecCount + 1
          Set pRow = pCursor.NextRow
      Loop Until (pRow Is Nothing)
      
      Debug.Print iRecCount