Forum

Optional filter in query

Yi Gang	#1 Dec 24, 2012 03:04 PM
Just came Points:: 0 Joined:: Dec 4, 2012	Hi！ I need to create a query which will have optional parameters。 When a company is specified it should filter by a company。 When it is not specified it should not filter by a company。
	Profile

ivan avdonin

Dec 24, 2012 03:24 PM

Active user

Points:: 0
Joined:: Sep 26, 2012

You can make so:

Code

   stringIF = "";
   //If ValueIsFilled(pOrganisation) Then // for common case
   If Not pOrganisation.IsEmpty() Then
      stringIF = "Where Doc1.Organisation = &Organisation";
   EndIf;
   
   Query = New Query;
   Query.Text = "
   |SELECT
   |   Doc1.Ref
   |FROM
   |   Document.Document1 AS Doc1
   |" + stringIF;
   
   Query.SetParameter("Organisation", pOrganisation);
   QueryResult = Query.Execute();

Profile

Yi Gang	#3 Dec 25, 2012 11:28 AM
Just came Points:: 0 Joined:: Dec 4, 2012	Thanks， Ivan， but is it possible to do this using a Query builder？ I like using this wizard。。。。。。
	Profile

Dmitrij Kazankov

Dec 25, 2012 02:11 PM

Just came

Points:: 0
Joined:: Sep 3, 2012

Yes, it is possible

Code

   QueryBuilder = New QueryBuilder;
   QueryBuilder.Text =
   "SELECT
   |   PurchaseOrder.Ref
   |FROM
   |   Document.PurchaseOrder AS PurchaseOrder
   |{WHERE
   |   PurchaseOrder.Company.*}";
   
   QueryBuilder.FillSettings();
   
   If ValueIsFilled(Company) Then
      Element = QueryBuilder.Filter.Add("Company");
      Element.Value = Company;
      Element.Use = True;
   EndIf;
   
   QueryBuilder.Execute();

Profile

Yi Gang	#5 Dec 25, 2012 04:06 PM
Just came Points:: 0 Joined:: Dec 4, 2012	Cool！
	Profile

Aleksey Bochkov

Dec 26, 2012 11:34 AM

Active user

Points:: 0
Joined:: Nov 19, 2012

Also you may do it like this (sometimes you can't use QueryBuilder):

Code

 
Query = New Query;
Query.Text = "
|SELECT
| Doc1.Ref
|FR OM
| Document.Document1 AS Doc1
| WH ERE 
|  CASE WHEN &OrganisationIsFilled 
|   THEN Doc1.Organisation = &Organisation 
|   ELSE TRUE END";

Query.SetParameter("Organisation", pOrganisation);
Query.SetParameter("OrganisationIsFilled ", ValueIsFilled(pOrganisation));

QueryResult = Query.Execute();

Profile