Diagrams are used here to show the filters that can be applied in a query through the Query object's Filters node, as illustrated by the example below:
In the following syntax diagrams:
- The green boxes represent information elements that are either written as they're shown (the ones in bold without italics) or can be replaced with the corresponding data (e.g.: att corresponds to a KB attribute), which in this case are shown in italics.
- The orange boxes correspond to a new definition given below.
The following diagram represents a valid entry for the Filters node. In other words: a valid filter.
According to the above, we have the following filter possibilities:
- CustomerId = 1
- CustomerId > 5
- CustomerId <> 10
- CustomerId in [1 to 5]
- CustomerId not in [1 to 5]
- CustomerId in [1,2,5,17,30]
- CustomerId not in [1,2,5,17,30]
- CustomerName like "Peter Pan"
- CustomerName not like "Peter Pan"
- CustomerId in [CustomerId where (ProductId = 10)]
- CustomerId not in [CustomerId where (ProductId = 10)]
- Sum( InvoiceAmount ) > 10000
- Count( CustomerId ) not in [4, 500, 1000]
- Average( InvoiceAmount) by InvoiceDate > 1000
- Sum( Average( InvoiceAmount) by InvoiceDate ) > 100000
||Parameter defined in the Query object's Parameter node
|Sum(Average(InvoiceAmount) by InvoiceDate)
|Sum( InvoiceAmount where (InvoiceAmount>100))
- It can't be the result of an expression like that of the horizontal formulas. E.g.: 25*constant1+&Balance
- It can't be an attribute.
When using a Date or Datetime attribute, the filter format is #YYYY-MM-DD# for Date and #YYYY-MM-DD HH:MM:SS# for Datetime; otherwise the following error will occur:
invalid date constant; ANSI format expected: YYYY-MM-DD HH:MM:SS.
They're used to specify a filter that is not a constant or a list of constant values, but in which values are drawn from the database.
Suppose we have a table that relates Supplier with Product sold.
We need to have a query that shows all the suppliers that sell product 5, and the amount of product that each supplier sells. To do that, we have to specify the query as follows:
- SupplierId in [SupplierId where (ProductId = 5)]
The subquery has to return one or more values, as applicable. If either =, >, >=, <, <=, of <> are used, it must return a single value. E.g.: att=[subquery].
If the query returns N values instead of the single value it should return, then the first value will be taken as the result of the subquery.
It should also return a single value when it is used with the in operator and a list of values that includes the subquery as one of those values. E.g.: att in [value1, value2, subquery, value3]. The list can be a list of clients 1, 2 and 3 plus the client that bought the most products (this client is obtained through a subquery). A single value is also returned when a range is established.
The only case in which the subquery can return N values is when it is used with an in operator that has a single element: the subquery. It's the example that returns all the suppliers that sold product 5 and the amount of product sold by these suppliers. That is: att in [subquery].
Also, to avoid having to specify a query:
(filter1 or filter2, or... or filterN) and (anotherFilter1 or anotherFilter2 or ... or anotherFilterM), you can specify groups of filters, as shown here with the following two, whose elements are linked by OR and both results are joined by AND:
That is, a filter group is used to place attributes between brackets and connect them with AND and OR.
Note that nesting is possible using Sum, Count and Average. Remember that the Where conditions the elements that are to be added; that is, it is applied before aggregating. For example, if you have Sum( InvoiceAmount where (InvoiceAmount > 1000)), only the invoices with amount > 1000 will be added together.
Note that nesting is possible using Sum, Count and Average. Remember that the Where conditions the elements that are to be counted; that is, it is applied before aggregating.
Note that nesting is possible using Sum, Count and Average. Remember that the Where conditions the elements that are to be averaged; that is, it is applied before aggregating.
Some differences of the Query object filters with filters specified in other GeneXus objects (procedures, web panels, data providers, etc.):
- Filters in the Query object must be resolved in the server, so no client logic invocations, such as an invocation to a data provider, can be made.
- Data Selectors cannot be used yet.
- Max and Min inline formulas have not implemented yet.