There's a special component intended to change SQL queries according to the end-user data browsing needs: QueryTransformer. It was made to meet the needs of programmers who want to let the end-users to transfigure data while browsing query result set in the grid. The component lets change data sorting and filtration while browsing the query result data in the grid, apply limits to the result set, and calcluate totals by columns.
It needs to be linked to the QueryBuilder and SQLBuilder objects to get the initial query columns and produce the modified query text in result.
Follow the simple steps below to change the query the way you need:
  1. Find the column you need in the QueryTransformer.Columns collection:
        column = queryTransformer.Columns.ColumnByName("OrderDate")
  2. Create an object that defines the necessary modification:
        sorting = column.Ascending()
        filter = column.NotLess("31/12/2012")
        aggregate = column.Max("DateMax")
  3. Apply these objects to the query:
        queryTransformer.OrderBy(sorting)
        queryTransformer.Where(filter)
        queryTransformer.Select(aggregate)
  4. Apply limits to the query:
        If queryTransformer.IsSupportLimitCount() Then
            queryTransformer.Take("50")
        End If
  5. Get the modified query from the QueryTransformer.SQL property:
        MessageBox.Show(queryTransformer.SQL)
    OR save the component state for future use with the QueryTransformer.StateXML property:
        savedState = queryTransformer.StateXML
        queryBuilder.SQL = updatedSql
        queryTransformer.StateXML = savedState
Below is the object relationship diagram and code samples for different languages.

Code samples

using (QueryTransformer queryTransformer = new QueryTransformer())
{
    queryTransformer.QueryBuilder = queryBuilder1;
    queryTransformer.SqlBuilder = plainTextSQLBuilder1;
 
    queryTransformer.BeginUpdate();
 
    try
    {
        // filter
        queryTransformer.Filters.Clear();
 
        queryTransformer
            .Where(queryTransformer.Columns[1].Not_Equal("100"))
            .Where(queryTransformer.Columns[2].In("('Value 1','Value 2','Value 3')"))
            .Where(queryTransformer.Columns.GetColumnByName("OrderId").Condition("> 100"))
            .Where("OrderDate is not null")
            .Where(FilterFactory.Or()
                .Add(queryTransformer.Columns[1].Not_Equal("100"))
                .Add(queryTransformer.Columns[1].Not_Equal("200")));
 
        // ordering
        queryTransformer.Sortings.Clear();
 
        queryTransformer
            .OrderBy(queryTransformer.Columns[1])
            .OrderBy(queryTransformer.Columns[2], false)
            .OrderBy(queryTransformer.Columns.GetColumnByName("OrderId"))
            .OrderBy("OrderDate");
 
        // limit, offset
        queryTransformer
            .Skip("100")
            .Take("50");
 
        // aggregations
        queryTransformer.Aggregations.Clear();
 
        queryTransformer
            .SelectRecordsCount("recordsCount")
            .Select(queryTransformer.Columns[1].Count(), "column1Count")
            .Select(queryTransformer.Columns[1].Sum(), "column1Sum")
            .Select(queryTransformer.Columns[1].Avg(), "column1Avg")
            .Select(queryTransformer.Columns[1].Min(), "column1Min")
            .Select(queryTransformer.Columns[1].Max(), "column1Max");
    }
    finally
    {
        queryTransformer.EndUpdate();
    }
 
    MessageBox.Show("Modified SQL", queryTransformer.Sql);
}
Using queryTransformer As New QueryTransformer()
    queryTransformer.QueryBuilder = queryBuilder1
    queryTransformer.SqlBuilder = plainTextSQLBuilder1
 
    queryTransformer.BeginUpdate()
 
    Try
        ' filter
        queryTransformer.Filters.Clear()
 
        queryTransformer.Where(queryTransformer.Columns(1).Not_Equal("100")). _
         Where(queryTransformer.Columns(2).[In]("('Value 1','Value 2','Value 3')")). _
         Where(queryTransformer.Columns.GetColumnByName("OrderId").Condition("> 100")). _
         Where("OrderDate is not null"). _
         Where(FilterFactory.[Or](). _
          Add(queryTransformer.Columns(1).Not_Equal("100")). _
           Add(queryTransformer.Columns(1).Not_Equal("200")))
 
        ' ordering
        queryTransformer.Sortings.Clear()
 
        queryTransformer. _
         OrderBy(queryTransformer.Columns(1)). _
         OrderBy(queryTransformer.Columns(2), False). _
         OrderBy(queryTransformer.Columns.GetColumnByName("OrderId")). _
         OrderBy("OrderDate")
 
        ' offset
        queryTransformer. _
         Skip("100"). _
         Take("50")
 
        ' aggregations
        queryTransformer.Aggregations.Clear()
 
        queryTransformer. _
         SelectRecordsCount("recordsCount"). _
         Select(queryTransformer.Columns(1).Count(), "column1Count"). _
         Select(queryTransformer.Columns(1).Sum(), "column1Sum"). _
         Select(queryTransformer.Columns(1).Avg(), "column1Avg"). _
         Select(queryTransformer.Columns(1).Min(), "column1Min"). _
         Select(queryTransformer.Columns(1).Max(), "column1Max")
 
    Finally
        queryTransformer.EndUpdate()
    End Try
 
    MessageBox.Show("Modified SQL", queryTransformer.Sql)
End Using
queryTransform.BeginUpdate;
try

       // filter
       queryTransform.Filters.Clear; // gets back the WHERE clause to initial state 

       queryTransform //adds conditions to the WHERE clause, conjunctioning it with conditions from initial query
           .Where(queryTransform.Columns[1].Not_Equal('100'))
           .Where(queryTransform.Columns[2].In_('(''Value1'',''Value2'',''Value3'')'))
           .Where(queryTransform.Columns.ColumnByName('OrderId').Condition('> 100'))
           .Where('OrderDate is not null')
           .Where(TacFilterFactory.Or_()
               .Add(queryTransform.Columns[1].Not_Equal('100'))
               .Add(queryTransform.Columns[1].Not_Equal('200')));

       // ordering
       queryTransform.Sortings.Clear; // gets back the ORDER BY clause to initial state 

       queryTransform // removes the ORDER BY clause of initial query and defines alternate ordering for a query
           .OrderBy(queryTransform.Columns[1])
           .OrderBy(queryTransform.Columns[2], false)
           .OrderBy(queryTransform.Columns.ColumnByName('OrderId'))
           .OrderBy('OrderDate');

       // offset
       queryTransform // applies the offset clauses to the query according to specific database server SQL syntax
           .Skip('100')
           .Take('50');

    // aggregations
    queryTransform.Aggregations.Clear;  // removes wrapping of the query in a sub-query

    queryTransform  // wraps the query in a sub-query and defines output columns for the outer query
    .SelectRecordsCount('recordsCount')
    .Select(queryTransform.Columns[1].Count, 'column1Count')
    .Select(queryTransform.Columns[1].Sum, 'column1Sum')
    .Select(queryTransform.Columns[1].Avg, 'column1Avg')
    .Select(queryTransform.Columns[1].Min, 'column1Min')
    .Select(queryTransform.Columns[1].Max, 'column1Max')
    ;

finally
       queryTransform.EndUpdate;
end;
Private Sub miTransform_Click()
Dim t As ActiveQueryBuilderXControls.aqbxQueryTransformer
Set t = ActiveQueryBuilderX1.QueryTransformer

t.BeginUpdate
On Error GoTo EndUpdate

' prepare
Dim column0 As ActiveQueryBuilderXControls.aqbxOutputColumn
Set column0 = t.Columns.Items(0)
Dim column1 As ActiveQueryBuilderXControls.aqbxOutputColumn
Set column1 = t.Columns.ColumnByName("OrderDate")


' Filtering
t.Filters.Clear

t.Where column0.Not_Equal(100)
t.Where column1.In("('Value 1','Value 2','Value 3')")
t.WhereExpr "CustomerName Like 'John%'"


' Sorting
t.Sortings.Clear

t.OrderBy column0.Ascending
t.OrderByColumn column1, False
t.OrderByExpr "CustomerName"


' limits
t.Take 50
t.Skip 100


' aggregations
t.Aggregations.Clear

t.SelectRecordsCount "recordsCount"
t.Select column0.Count, "column0Count"

EndUpdate:
t.EndUpdate

MsgBox t.SQL
End Sub

Is this article helpful for you?