Query object: Filters

Official Content
This documentation is valid for:

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:

Query Object - Screen Capture

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.

Valid filter:

The following diagram represents a valid entry for the Filters node. In other words: a valid filter.

Query Object - filter 

According to the above, we have the following filter possibilities:

  1. CustomerId = 1
  2. CustomerId > 5
  3. CustomerId <> 10
  4. CustomerId in [1 to 5]
  5. CustomerId not in [1 to 5]
  6. CustomerId in [1,2,5,17,30]
  7. CustomerId not in [1,2,5,17,30]
  8. CustomerName like "Peter Pan"
  9. CustomerName not like "Peter Pan"
  10. CustomerId in [CustomerId where (ProductId = 10)]
  11. CustomerId not in [CustomerId where (ProductId = 10)]
  12. Sum( InvoiceAmount ) > 10000
  13. Count( CustomerId ) not in [4, 500, 1000]
  14. Average( InvoiceAmount) by InvoiceDate > 1000
  15. Sum( Average( InvoiceAmount) by InvoiceDate ) > 100000

Valid values:

Query Object - value

Examples:

1 Numeric Constant
"Peter" Character Constant
#2009-12-31# Date Constant
#2009-12-31 08:55:00# DateTime Constant
&Balance Parameter defined in the Query object's Parameter node
Sum(InvoiceAmount) Sum
Count(CustomerId) Count
Average(InvoiceAmount) Average
Sum(Average(InvoiceAmount) by InvoiceDate) Sum
Sum( InvoiceAmount where (InvoiceAmount>100)) Sum

Constraints

  • 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.

Date and Datetime Filters

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.


Valid subqueries:

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.

Query Object - subquery

Example:

Suppose we have a table that relates Supplier with Product sold.

SupplierId*
ProductId*

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:

Atts:

  • SupplierName
  • ProductDescription

Filters:

  • SupplierId in [SupplierId where (ProductId = 5)]

Observations:

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].


Filters made up of logical operators:

Query Object - filters

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:

Query Object - Group of Filters

That is, a filter group is used to place attributes between brackets and connect them with AND and OR.


Valid Sum:

Query Object - Sum

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.


Valid Count:

Query Object - Count

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.


Valid Average:

Query Object - Average

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.




Was this page helpful?
What Is This?
Your feedback about this content is important. Let us know what you think.