How To...Search on the Not Null

Question 

"How can I use SQLBuilder to search for values that are not null?" 

Solution

Use the Report.OnInitializeParaemeters or BeforeOpenDataPipelines event and SQLBuilder to add a SearchCriteria item and specify 'Is Not Null' for the operator.

Download:  SQLBuilderAddNotNullSearch.zip

Sample Delphi code

uses
  SQLBuilder;

procedure TForm1.ppReport1InitializeParameters(Sender: TObject; var aCancel: Boolean);
var
  lSQLBuilder: TdaSQLBuilder;

begin

  ppReport1.DataPipeline.Close;

  lSQLBuilder := TdaSQLBuilder.Create(ppReport1);
  lSQLBuilder.SearchCriteria.Clear;

  lSQLBuilder.SearchCriteria.Add('Customer', 'State', 'IS NOT NULL', '');

  lSQLBuilder.Free;

end;