English

How To: 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

SQL functions are used through the SubFields method of IQueryFilter. Most of these functions return a single value. The field name returned depends on the database, but is usually blank. You can use the AS function to assign a name. The DISTINCT function returns a cursor of distinct values. The column should be indexed if any of these functions is to be called routinely.

  • 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