Complex WHERE Clauses Code Sample

Key Objects Used

• ISSelectQueryBuilder
• finBL.TimeZoneFunctions

Code

Dim sqb As ISSelectQueryBuilder = finBL.Database.CreateSelectQueryBuilder()
Dim sqbAddresses As ISSelectQueryBuilder = finBL.Database.CreateSelectQueryBuilder()

' Main query
With sqb
  .Table = "Client"
  .Fields.AddList("Pk,ClientId,Name,DateOfBirth,Status")

  With .SqlWhere
    ' Sub-query: find clients with matching addresses
    With sqbAddresses
      .Table = "ClientContactAddress"
      .Fields.Add("ClientPk")

      With .SqlWhere
        .AppendComparisonField("ClientContactAddress.ClientPk", "=", "Client.Pk")

        ' OR block: match City or Suburb
        .BlockBegin(iseSqlWhereBuilderNestedBlockType.OrBlock)
        .AppendRange("City", citiesRange)
        .AppendRange("Suburb", citiesRange)
        .BlockEnd()

        ' Current addresses only
        .AppendComparisonIntegerBoolean("Historic", "=", False)
        .BlockBegin(iseSqlWhereBuilderNestedBlockType.OrBlock)
        .AppendComparisonNull("DateStop")
        .AppendComparisonDate("DateStop", "<", finBL.TimeZoneFunctions.GetCurrentDatabaseDate().AddDays(1), False)
        .BlockEnd()
      End With
    End With

    .AppendInSubQuery("Pk", sqbAddresses)

    ' Always apply user filters
    .Append(finBL.CurrentUserInformation.FilterClientSqlWhere)
  End With

  .OrderByFields.Add("ClientId")
End With

' Execute into a DataTable
Dim dt As DataTable
sqb.ExecuteDataTable(dt)