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:
The following diagram represents a valid entry for the Filters node. In other words: a valid filter will be composed of:
Based on the previous initial definition and the following detail, possible filters values are:
CustomerId = 1
CustomerId > 5
CustomerId <> 10
CustomerId in (1 to 5)
not CustomerId in (1 to 5)
CustomerId in (1,2,5,17,30)
not CustomerId in (1,2,5,17,30)
CustomerName like "Peter Pan"
not CustomerName like "Peter Pan"
CustomerId in (CustomerId where (ProductId = 10))
not CustomerId in (CustomerId where (ProductId = 10))
Sum( InvoiceAmount ) > 10000
not Count( CustomerId ) in (4, 500, 1000)
Average( InvoiceAmount) by InvoiceDate > 1000
||Parameter defined in the Query object's Parameter node
|Sum(Average(InvoiceAmount) by InvoiceDate)
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 a relational operator is 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).
An example of different kind of filters can be the following:
If you need to combine AND and OR will need to specify it explicitly for example link them by OR and both results are joined by AND:
(filter1 or filter2, or... or filterN) and (anotherFilter1 or anotherFilter2 or ... or anotherFilterM)
(CustomerId > 5 or CustomerId <> 10 or CustomerId in (1 to 5) or CustomerId in (CustomerId where CustomerName <> "Peter Pan")) and (not CustomerName like "Hook" or CustomerId in (1,2,3,4))
Valid Sum | Count | Average | Min | Max:
Nesting is possible using Sum, Count, Average, Max and Min. 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.
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.
This behavior is available since GeneXus 16 Upgrade 11.