Filtering Data

Result := TBISQL.From(Data, 'ProductName, Price where Price > 50');

// Equal
Result := TBISQL.From(Data, 'Name where Customer = 123');

// Not equal
Result := TBISQL.From(Data, 'Name where Status <> "Inactive"');

// Greater than / Less than
Result := TBISQL.From(Data, 'Name where Price > 100');
Result := TBISQL.From(Data, 'Name where Quantity < 10');

// Greater or equal / Less or equal
Result := TBISQL.From(Data, 'Name where Price >= 100');
Result := TBISQL.From(Data, 'Name where Quantity <= 10');

Result := TBISQL.From(Data, 
  'ProductName where (Price > 50) and (Category = "Electronics")');
  
Result := TBISQL.From(Data,
  'sum(Amount) where (Customer=123) and (Product<456) group by Country');

Result := TBISQL.From(Data, 
  'ProductName where (Category = "Electronics") or (Category = "Books")');

Result := TBISQL.From(Data, 
  'ProductName where not (Price > 100)');

// Static list
Result := TBISQL.From(Data, 
  'ProductName where Category in ["Electronics", "Books", "Toys"]');

// Sub-query
Result := TBISQL.From(Data, 
  'ProductName where CategoryID in select ID from Categories where Active=true');

Result := TBISQL.From(Data, 
  'ProductName where ProductName starts "Pro"');

Result := TBISQL.From(Data, 
  'ProductName where ProductName ends ".com"');

Result := TBISQL.From(Data, 
  'ProductName where Description contains "premium"');

// Compare to aggregate
Result := TBISQL.From(Data, 
  'ProductName, UnitPrice where UnitPrice > select Average(UnitPrice)');

// Using IN with sub-query
Result := TBISQL.From(Data,
  'OrderID where CustomerID in select ID from Customers where Country="USA"');

var
  Query: TBIQuery;
begin
  Query := TBIQuery.Create(nil);
  try
    Query.Data := MyData;
    
    // Set filter as text
    Query.Filter.Text := 'Price > 100';
    
    // Or use custom expression
    Query.Filter.Custom := TLogicalExpression.Create(
      TDataItemExpression.Create(MyData['Price']),
      TLogicalOperand.Greater,
      TIntegerExpression.Create(100)
    );
    
    Result := Query.Calculate;
  finally
    Query.Free;
  end;
end;

var
  Select: TDataSelect;
begin
  Select := TDataSelect.Create(nil);
  try
    Select.Data := MyData;
    Select.Add(MyData['ProductName']);
    Select.Add(MyData['Price']);
    
    // Set filter expression
    Select.Filter := TLogicalExpression.Create(
      TDataItemExpression.Create(MyData['Price']),
      TLogicalOperand.Greater,
      TIntegerExpression.Create(100)
    );
    
    Result := Select.Calculate;
  finally
    Select.Free;
  end;
end;

var
  Filter1, Filter2, Combined: TLogicalExpression;
begin
  // Price > 100
  Filter1 := TLogicalExpression.Create(
    TDataItemExpression.Create(MyData['Price']),
    TLogicalOperand.Greater,
    TIntegerExpression.Create(100)
  );
  
  // Category = "Electronics"
  Filter2 := TLogicalExpression.Create(
    TDataItemExpression.Create(MyData['Category']),
    TLogicalOperand.Equal,
    TTextExpression.Create('Electronics')
  );
  
  // Combine with AND
  Combined := TLogicalExpression.Join(Filter1, Filter2, TLogicalOperand.&And);
  
  Select.Filter := Combined;
end;

// Using SQL
Result := TBISQL.From(Data, 'distinct Country, City');

// Using TDataSelect
Select.Distinct := True;

// First 100 rows
Result := TBISQL.From(Data, 'top 100 ProductName, Price');

// Skip 50, take 100
Result := TBISQL.From(Data, 'top 100 offset 50 ProductName, Price');

Select.Max := 100;    // Maximum rows to return
Select.Start := 50;   // Rows to skip

var
  MasterData, DetailData: TDataItem;
  DetailIndex: TCursorIndex;
begin
  // Get filtered detail records for master row 5
  DetailIndex := TDataCursor.MasterDetailIndex(
    DetailData,
    TDataArray.Create([MasterData]),
    5
  );
end;

Select.UseFilter := False; // Temporarily disable filtering

var
  Cursor: TDataCursor;
begin
  Cursor.Filter := MyFilterExpression;
  Cursor.PrepareIndex; // Build index
  
  // Access filtered rows efficiently
  for I := 0 to Cursor.Count - 1 do
    Position := Cursor.Index[I];
end;

var
  Summary: TSummary;
begin
  Summary := TSummary.Create(nil);
  try
    Summary.Measures.Add(MyData['Sales'], TAggregate.Sum);
    Summary.By.Add(MyData['Country']);
    
    // Filter aggregated results
    Summary.Having.Add('Sum(Sales) > 10000');
    
    Result := Summary.Calculate;
  finally
    Summary.Free;
  end;
end;

function FilterErrorHandler(
  const APos: Integer;
  const AMessage: String
): Boolean;
begin
  ShowMessage(Format('Filter error at position %d: %s', [APos, AMessage]));
  Result := True; // Don't raise exception
end;

Result := TBISQL.From(Data, 
  'ProductName where invalid syntax', 
  nil, 
  FilterErrorHandler
);