Optional filter in query

Common questions about 1C:Query language, Query builder tool and Data composition schema

Pages: 1

Yi Gang	#1 People who like this:0 Yes/0 No Dec 24, 2012 03:04 PM
Just came Rating: 1 Joined: Dec 4, 2012 Company: China Welfare Lottery	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。

ivan avdonin

People who like this:0 Yes/0 No

Dec 24, 2012 03:24 PM

Active user

Rating: 7
Joined: Sep 26, 2012
Company: individual

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();

Edited: ivan avdonin - Dec 25, 2012 12:21 PM

Yi Gang	#3 People who like this:0 Yes/0 No Dec 25, 2012 11:28 AM
Just came Rating: 1 Joined: Dec 4, 2012 Company: China Welfare Lottery	Thanks， Ivan， but is it possible to do this using a Query builder？ I like using this wizard。。。。。。 Edited: Yi Gang - Dec 25, 2012 11:29 AM

Dmitrij Kazankov

People who like this:1 Yes/0 No

Dec 25, 2012 02:11 PM

Just came

Rating: 0
Joined: Sep 3, 2012
Company:

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();

Yi Gang	#5 People who like this:0 Yes/0 No Dec 25, 2012 04:06 PM
Just came Rating: 1 Joined: Dec 4, 2012 Company: China Welfare Lottery	Cool！

Aleksey Bochkov

People who like this:0 Yes/0 No

Dec 26, 2012 11:34 AM

Active user

Rating: 4
Joined: Nov 19, 2012
Company:

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();

Pages: 1

Users browsing this topic (guests: 1, registered: 0, hidden: 0)

Optional filter in query

Download brochures